By the end of this article, you will learn about the most important permissions within a SQL Server database, how they are split into server-level and database-level permissions.
Also, you will learn how you can grant users access to different levels of permissions through either a visual interface provided by SSMS (SQL Server Management Studio) or through T-SQL queries.
SQL Server user permissions
Let’s assume you have a new user who has to have access to your system and implicitly access to your database.
First, we should go through some of the most important permissions that can be granted to a user and how we can apply these to the user, either through a visual interface like SSMS (SQL Server Management Studio) or through Microsoft’s T-SQL language.
Before starting we should know that there are two major levels of permissions that can be granted to a user:
- Server-level permissions that give the user server-wide access and implicitly access to all databases that reside on that server.
SQL Server provides nine default server level roles that contain different sets of permissions and they cannot be changed. Beginning with SQL Server version 2012, you can create your own user-defined roles that can be applied at the server level and also even add server-level permissions to these new server roles. The following is the list of default server-level roles that SQL Server comes with is:
- sysadmin – can perform any activity on the server
- serveradmin – can change server-wide configurations and shut down the server
- securityadmin – can manage logins and configure server logins
- processadmin – have full access to processes that run in a SQL Server instance
- setupadmin – have the ability to add or remove linked servers
- bulkadmin – have access to bulk insert data
- diskadmin – has access to manage disk files for a database instance
- dbcreator – can create, alter, drop or restore any database on the SQL Server
- public – all SQL Server logins belong to this role and all users inherit permissions from this role
You can set up these roles from within SSMS by following the next steps:
- Connect to your database instance, and then go to Security -> Logins and select the login credential you want to grant permissions to. Right-click the login and select Properties.
- A new pop-up will appear and by navigating to the “Server Roles” page you can see the list of server-level permissions that can be applied to this login.
- Database-level permissions give access to certain databases and hence a user can have different levels of permission on different databases within the same server.
The following are the different database-level permissions provided by SQL Server:
- db_owner – can perform all configuration and maintenance activities on the database
- db_securityadmin – can modify role membership for roles that have been created by a administrator or another user (user-defined roles)
- db_accessadmin – can add or remove access to the database for Windows logins and groups
- db_backupoperator – can back up the database
- db_ddladmin – can run any DDL command in a database
- db_datawriter – can add, delete or change data in all user-defined tables
- db_datareader – can read all data from user-defined tables
- db_denydatawriter – users cannot add, modify or delete any data in user-defined tables
- db_denydatareader – users cannot read any data in user-defined tables
You can grant database level permissions through the Object Explorer window, right-click on the database and select the Properties option.
A new pop-up will appear and if you go to Permissions, press the Search button, you can add any new users to this database. After adding them, you will see a list of permissions that can be granted to the user in the bottom part of the pop-up window.
You can view all of the grantable permissions to a user by also using the following T-SQL query:
SELECT * FROM fn_builtin_permissions(default);
Grant permissions to a database for a new user using T-SQL
Granting usage to a database for a specific user is something that every database administrator needs to be able to do, once a new member arrives on the team or an old one leaves.
Since we already showed how you can manually add permissions through SSMS, we will continue and show the T-SQL variants for achieving the same results.
Granting usage onto a database for a user is a multi-step process, which is listed below:
1. If you already have a user created then you can skip this step
CREATE LOGIN [<domainName>\<loginName>];
2. You need to grant this login the permission to access a database. A Login is required to give user access to the SQL Server instance. Afterward, this login is associated with a user, which is created at the server level.
USE <database> CREATE USER <username> FOR LOGIN <login>;
3. Once the user has been assigned to the database then you can give it any rights you want
USE <database> EXEC sp_addrolemember ’db_datareader’, ‘<username>’;
Grant view permissions
Granting view permissions to a user is synonymous to granting SELECT permissions which can be done by running the following query:
GRANT SELECT ON <object_name> TO <username>;
Grant execute SQL stored procedures
SQL Server stored procedure permissions are granted by using the following T-SQL query:
GRANT EXECUTE ON spGetLatestOrders TO <username>;
However, if you want to allow a user to modify or delete a stored procedure, you need to run the specific statement from the following list:
Modify a stored procedure
GRANT MODIFY ON spGetLatestOrders TO <username>;
Delete a stored procedure
GRANT DELETE ON spGetLatestOrders TO <username>;
Grant table access
Grant table permissions are similar to granting permissions to views, so the solution is:
GRANT SELECT ON <table> TO <username>;
Grant table alter
Another common operation ran on tables is adding or removing columns. For this, the ALTER SQL table permission is required. In order to grant alter permissions, you need to run a query similar to:
GRANT ALTER ON <table> TO <username>;