SQL Server security architecture contains a hierarchical collection of entities, starting with server, database and objects (also called levels) and SQL Server provides a set of fixed roles to easily manage the permissions.
We often create logins for DBAs using the sysadmin role and this role gives access to everything. Normally, database administrators donít need permissions to insert, update or delete records, and denying this kind of access is the first step to improve your security in your production environments.
First, letís see who has sysadmin access, by executing the following query to see the fixed roles granted for the principals:
SELECT SP1.name AS ServerRoleName, ISNULL(SP2.name, 'No members') AS LoginName FROM sys.server_role_members AS SRM RIGHT OUTER JOIN sys.server_principals AS SP1 ON SRM.role_principal_id = SP1.principal_id LEFT OUTER JOIN sys.server_principals AS SP2 ON SRM.member_principal_id = SP2.principal_id WHERE SP1.is_fixed_role = 1 and SP1.name = Ďsysadminí ORDER BY SP1.name; GO
The next step is remove the sysadmin role from the logins we want to restrict access to and grant CONTROL SERVER permission for that login.
Control Server and sysadmin have the same kind of permissions and with that they can manage the entire database server instance. But, giving CONTROL SERVER permissions we can also deny updates, deletes and inserts when we map the login with the database user.
At this point we know which logins we need to change and what kind of permission to use. Now, we can define a new server role and a database role for our database administrator and separate activities like monitoring, backup, maintenance, etc.
Creating a new SQL Server server role
Here is the code to create a new SQL Server server role, with the Control Server access.
USE [master] GO CREATE SERVER ROLE [dbaadmin] GO GRANT CONTROL SERVER TO [dbaadmin] GO
Mapping the database user with the db_owner database fixed role will grant more access than necessary, so we will create our own database role with limited permissions. We will do this for the AdventureWorks2012 database.
Creating a new SQL Server database role
Here are denying update and delete privileges for this database role.
USE [AdventureWorks2012] GO CREATE ROLE [db_dbaadmin] GO DENY UPDATE TO [db_dbaadmin] GO DENY DELETE TO [db_dbaadmin] GO
Also, there are more permissions we will deny in this example.
DENY ALTER TO db_dbaadmin GO DENY EXECUTE TO db_dbaadmin GO
Additional information about permissions
The most specific and limited permissions that can be granted on an object are listed in the following table:
|ALTER||ALTER permission on a schema includes the ability to create, alter, and drop objects from the schema|
|CONTROL||CONTROL on a database implies all permissions on the database|
|DELETE||Deletes all classes of objects except DATABASE SCOPED CONFIGURATION, and SERVER|
|EXECUTE||To run CLR types, external scripts, procedures, scalar and aggregate functions|
|INSERT||Insert records using synonyms, tables and views objects. Permission can be granted at the database, schema, or object level.|
|RECEIVE||Service Broker queues|
|REFERENCES||The REFERENCES permission on a table is needed to create a FOREIGN KEY constraint that references that table|
|SELECT||Select synonyms, tables and columns, views and columns. Permission can be granted at the database, schema, or object level. Also, is necessary to see the executing plan|
|TAKE OWNERSHIP||Enables the grantee to take ownership of the securable on which it is granted|
|UPDATE||Update records using synonyms, tables and views objects. Permission can be granted at the database, schema, or object level.|
|VIEW CHANGE TRACKING||Manage change tracking on schemas and tables|
|VIEW DEFINITION||Enables the grantee to access metadata|
Read the following to learn more about deny permissions on a database and server in SQL Server:
Create the SQL Server login and assign roles
After designing our own roles, the next step is to create the logins and users.
In the example below, we will create a new login named dbaLogin and add this login to the dbaadmin server role and the db_dbadmin database role.
Note, if the login already exists we only need to add the server role for the login, remove the sysadmin fixed role and add the user to the database and add the user to the database role.
USE [master] GO CREATE LOGIN [dbaLogin] WITH PASSWORD=N'sql', DEFAULT_DATABASE=[AdventureWorks2012], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO ALTER SERVER ROLE [dbaadmin] ADD MEMBER [dbaLogin] GO USE [AdventureWorks2012] GO CREATE USER [dbaUser] FOR LOGIN [dbaLogin] GO USE [AdventureWorks2012] GO ALTER ROLE [db_dbaadmin] ADD MEMBER [dbaUser] GO
Testing the new SQL Server roles
We will test the login by executing some code in the user database. As expected, these commands fail.
But if we run a system stored procedure like sp_who2 it returns the necessary results.
In addition, CONTROL SERVER gives permission to add or remove members from any role, but when we denied the ALTER permission the login can't change its own permission as follows.
As stated before, configuring our own roles is a fast and easy way to control server and database access in SQL Server. Also your security will be in compliance with the company's security policies.
Creating different access for specifics activities is a good way to have a better security solution. For example, the application login doesnít need permission to alter objects and with the right security solution you can find out fast what happened in your environment using audit filters for a specific login.
Take this as a starter to begin defining better security roles for your SQL Server environment.
- Read these additional articles:
Last Update: 2017-07-26
About the author
View all my tips