Giving and removing permissions in SQL Server

SQL Server offers three pretty 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 Joe and Mary to SELECT, INSERT and UPDATE data in table Customers

GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary

Revoke UPDATE access to table Customers for user Joe

REVOKE UPDATE ON Customers to Joe

DENY DELETE access to table Customers for user Joe and Mary

DENY DELETE ON Customers to Joe, Mary

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 Joe

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 Joe

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.

Next Steps

• Take a look at this tip on how to determine what permissions have been granted Auditing your SQL Server database and server permissions
• Take a look at these commands for other examples
  • GRANT
  • DENY
  • REVOKE
  • sp_helprotect