SQL Server 2014: protection against Database Administrator

Such a topic related to SQL Server 2014 CTP1 may seem a bit strange, but it was chosen not randomly. The questions like “How can I protect my data against the DBA?” appeared on forums regularly.

Until recently this problem was not solved, but thanks to the emergence of 3 new server-level permissions in SQL Server 2014 (CONNECT ANY DATABASE, IMPERSONATE ANY LOGIN and SELECT ALL — USER SECURABLES) now we are more flexible in managing server roles. In particular, we can create an administrator’s server role with total restriction on access to user data.

Let me remind you that for the first time the option to create server roles appeared in SQL Server 2012 (CREATE SERVER ROLE) and in SQL Server 2014 developers went a little further.


But let’s go step by step. First, we create a new server role NewServerRole that belongs to the fixed server role sysadmin (members of sysadmin fixed server role can perform any activity on the server).

create server role NewServerRole authorization sysadmin; go

Now for demonstration purposes we create a new login and include it in our server role NewServerRole. Do not forget to set up mixed mode authentication before (right-click on our database server => Properties => Security => Server authentication) and restart SQL Server.

sysadmin

create login NewLogin with password = 'Qwerty1'; go alter server role NewServerRole add member NewLogin; go

Now we grant CONTROL SERVER rights to our server role

grant control server to NewServerRole; go

Now we can connect to our database server under a new login NewLogin and carry out a set of instructions to make sure that we have full rights.

--Сreate new login create login NewLogin2 with password = 'Qwerty1'; go -- Change server options exec sp_configure 'show advanced options', 1; reconfigure; go exec sp_configure 'xp_cmdshell', 1; reconfigure; go --Execute xp_cmdshell exec xp_cmdshell 'dir c:'; go --Go to data from master database select * from master..spt_values; go

And now we restrict our server role access to all user data:

deny select all user securables to NewServerRole; go

Next step is to start session under our test login NewLogin and try to run the following script:

--Create new login create login NewLogin3 with password = 'Qwerty1'; go --Change server options exec sp_configure 'show advanced options', 1; reconfigure; go exec sp_configure 'xp_cmdshell', 1; reconfigure; go --Run xp_cmdshell exec xp_cmdshell 'dir c:'; go --Go to data from master database select * from master..spt_values; go

As a result we will be able to carry out all the steps except the last one:

The SELECT permission was denied on the object ‘spt_values’, database ‘master’, schema ‘dbo’.

 Below is a number of scenarios with new permissions:

  • Smithg366 says:

    I really appreciate this post. I’ve been looking all over for this! Thank goodness I found it on Bing. You’ve made my day! Thanks again!

  • JeremyH says:

    Wouldn’t an administrator be able to just grant the privileges back to his/her self?

  • Leave a comment