SQL SERVER – Creating a Database Administrator Group Using User-Defined Server Roles

When it comes to security related topics, I always read and learn something new from my friend Vinod. He always comes-up with something interesting and when he was at GIDS event, one of the attendees walked and asked a security question. He was interested in knowing how we can have near to DBA privileges and still mask the data from the DBA because it has sensitive information. Without an iota of doubt, I just pointed to Vinod to take the conversation forward. Curious enough, I made it a point to join the discussion immediately. Who would want to miss an opportunity to learn something new anyways?

By the time I reached, the explanation had started and here is what I was hearing. Once that learning – which in my opinion was in air in general. I had to tryout what he said. Let me do a recap of the same:

The sysadmin role has access to everything inside SQL Server. It is not possible to restrict access to logins who are members of the sysadmin role.  In some cases, you may want to have users who are DBAs but you do not want them to access sensitive tables or change certain database schemas.  In SQL Server 2012 you can leverage a new feature called user-defined server roles to provide a solution to this scenario.

In this exercise, you have a SALES database that contains two users, “PinalUser” and “SQLAuthUser”.  SQLAuthUser owns a schema called, “CRM”.  In this schema there is an Employees table which contains sensitive information. PinalUser is a DBA and our task is to create a server scoped user role called, “DBA Role” which PinalLogin is a member.  Note: PinalLogin is mapped to PinalUser.  By the end of this lab you will see how even though PinalLogin is a member of DBA Role and that role has CONTROL SERVER permissions, he can’t SELECT the sensitive information from the CRM schema.

To simulate and showcase the same, below are the 6 steps to be taken:

Create Sales database, logins and users

Open SQL Server Management Studio, In the Query Editor that was created as a result of the previous step, enter the following code and press F5 (or click on the “Execute” button on the toolbar):

USE MASTER
GO
CREATE DATABASE Sales
GO
CREATE LOGIN PinalLogin WITH PASSWORD='pass@word1'
GO
CREATE LOGIN SQLAuthLogin WITH PASSWORD='pass@word1'
GO
USE Sales
GO
CREATE USER PinalUser FOR LOGIN PinalLogin
GO
CREATE USER SQLAuthUser FOR LOGIN SQLAuthLogin
GO
CREATE SCHEMA CRM AUTHORIZATION SQLAuthUser
GO
CREATE TABLE CRM.Employees
(Employee_name VARCHAR(50) NOT NULL,
Emplpoyee_SS CHAR(12) NOT NULL,
Employee_DOB DATE NOT NULL)
GO
INSERT INTO CRM.Employees VALUES('Vinod','123-22-3456','12/1/1980'),('Balu','345-55-1234','4/3/1985')
GO


Connect to SQL Server as PinalLogin and see if he has access to the Employees table

In Object Explorer click on the Connect button and select, “Database Engine…”. Connect as PinalLogin. If the connection was successful you will see another node in the object explorer for the connection to the database under PinalLogin context. You can tell the connection context by looking at the last part of the name of the server node. Navigate down PinalLogin’s connection to the Databases node, then to the Sales node then to the Tables node.

Note: The CRM.Employees table does not exist as Pinal doesn’t have access to the CRM schema.

As the sysadmin, create a server level role, DBA Role and add Pinal to it

In a query editor, please type the below commands:

USE MASTER
GO
CREATE SERVER ROLE [DBA Role] GO
GRANT CONTROL SERVER TO [DBA Role] GO
DENY ALTER ANY SERVER AUDIT TO [DBA Role] GO
DENY ALTER ANY LOGIN TO [DBA Role] GO
DENY ALTER ANY SERVER ROLE TO [DBA Role] GO
ALTER SERVER ROLE [DBA Role] ADD MEMBER PinalLogin
GO


Note: If these commands failed, make sure your query editor window is running under the context of the administrator.  You can tell this by looking at the connection properties dialog (hitting F4).  The Login name will show you the login context of the current query editor window.

Now that PinalLogin is a member of the DBA Role and this role has CONTROL SERVER permission let’s see if PinalLogin can now access the table.

As PinalLogin, query the CRM.Employees table

1. In Object Explorer, select the server node that is connected as “PinalLogin”
2. Right click and select, “Refresh” from the context menu
3. Navigate down to, “Databases” then “Sales” then “Tables”
4. Click on Tables node and select the “CRM.Employees” table
5. Right click and select, “Select Top 1000 Rows” from the context menu

A new query editor window should open showing you the contents of the CRM.Employees table. This is because PinalLogin has CONTROL SERVER permission. A permission that is almost equivalent to sysadmin. Close the document window showing you the CRM.Employees result set by clicking on the “X” or by selecting, “Close” from the “File” menu.

As an administrator, deny select to PinalUser

PinalUser is the database user that is mapped to the PinalLogin server principal.  To ensure Pinal can’t see out sensitive employee table, we simply issue a DENY statement on PinalUser. In the query editor window enter the following code:

USE SALES
GO
DENY SELECT ON SCHEMA::CRM TO PinalUser
GO


Note: If these commands failed, make sure your query editor window is running under the context of the administrator.  You can tell this by looking at the connection properties dialog (hitting F4).  The Login name will show you the login context of the current query editor window.

The big difference between sysadmin and someone with CONTROL SERVER permission is SQL Server will respect a DENY on an object even when the user has CONTROL SERVER. In our example, even though PinalLogin has CONTROL SERVER, since database user PinalUser has a DENY on the CRM.Employees table, he can’t see the data.

As  PinalLogin try to query the CRM.Employees table

Follow these steps:

1. In Object Explorer, select the server node that is connected as “PinalLogin”
2. Right click and select, “Refresh” from the context menu
3. Navigate down to, “Databases” then “Sales” then “Tables”
4. Click on Tables node and select the “CRM.Employees” table
5. Right click and select, “Select Top 1000 Rows” from the context menu

Expected Outcome: 

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object ‘Employees’, database ‘Sales’, schema ‘CRM’.

This completes our steps for restricting a DBA user to access sensitive data.  I am curious to know; have you ever done this before? I would love to hear from you and your learnings on this subject.

Reference: Pinal Dave (https://blog.sqlauthority.com)