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.
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:
- DBA with no access to user data (as in the example above)
- DBA with no right to change logins
- DBA with no right to give permission impersonate (to replace context) for other logins
- The role with the opportunity to read ALL user data without administrator rights
- The role with the opportunity to view different metadata, without access to user data
- The role with the opportunity to impersonate yourself with any login except for those with administrative privileges (sa, sysadmin)
