Permissions allow users to do thing in the database. The owner of an object (eg a table) in a database can allow or disallow other user's actions on that object. Here's a list of things that can be allowed or disallowed using permissions:
Object Permissions
- SELECT
- INSERT
- UPDATE
- DELETE
- EXECUTE (lets stored procedures be executed)
- REFERENCES (lets the user refer to a table using WHERE)
- ALL (gives the user all permissions)
Statement Permissions
- CREATE DATABASE
- CREATE DEFAULT
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
- BACKUP DATABASE
- BACKUP LOG
- ALL (all of the other statements)
How do you allow/disallow these actions? You use the following commands:
- GRANT
- DENY
- REVOKE (to "undo" a grant command).
Here are some examples of Grant, Deny and Revoke statements - use these to go off and try a few of your own.
- grant insert, update on authors to myuser
- deny delete on authors to myuser
- revoke update on authors to myuser
Finally, you can use the sp_helprotect stored procedure to check existing permissions in the database, so be sure to practise this one.