Problem
Often times when auditing SQL Servers I notice that most environments use the default database roles to grant access to users. These default roles such as db_owner, db_datareader and db_datawriter are great, but often times they give the users a lot more access then they really need. Giving permissions and removing permissions is not that hard to do within SQL Server, it just takes some time to determine what permissions should be applied.
Solution
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
Greg Robidoux has been working with databases for 35+ years with extensive hands on SQL Server experience from version 6.5 to 2025. He has authored over 250 technical articles and delivered several presentations online and at various conventions. Greg is also the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server.
