Giving and removing permissions in SQL Server

SQL Server offers three simple commands to give and remove access, these commands are:

• GRANT - gives a user permission to perform certain tasks on database objects
• DENY - denies any access to a user to perform certain tasks on database objects
• REVOKE - removes a grant or deny permission from a user on certain database objects

Here are some examples of these commands.


Allow users PO1 and PO2 to SELECT, INSERT and UPDATE data in table Customers
GRANT INSERT, UPDATE, SELECT ON Customers TO PO1, PO2
Revoke UPDATE access to table Customers for user PO1
REVOKE UPDATE ON Customers to PO1
DENY DELETE access to table Customers for user PO1 and PO2
DENY DELETE ON Customers to PO1, PO2
As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:
GRANT EXEC ON uspInsertCustomers TO PO1
To determine what rights have been granted in a database use the sp_helprotect stored procedure.
In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc...  To grant a user permissions to create a table you would run this command.
GRANT CREATE TABLE TO PO1
As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.