Tighten SQL Server security with custom server and database roles

Problem

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.

Solution

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:

Read the following to learn more about deny permissions on a database and server in SQL Server:

• Deny server permissions
• Deny database permissions

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.

Conclusion

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.

The downside to this is if you restore a SQL Server database to another environment, you will need to remap the server logins and database users.

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.

Next Steps

• Read these additional articles:
  • Understanding SQL Server fixed server roles
  • Server level roles docs
  • SQL Server Security Tips

Douglas Correa

Douglas Correa is a database professional focused on performance tuning database, high-availability, and infrastructure. He has been working with SQL Server for more than ten years. He has performance-tuned databases ranging from 1 gigabyte to terabytes working with PowerShell, SQL Server and Oracle. He has certifications from Microsoft (MCSA SQL Server, MTA Network) and Oracle (OCE, OCA 11g).