SQL Server 2014 improves the segregation of duties by implementing new server permissions. The most important is the SELECT ALL USERS SECURABLES permission that will help to restrict database administrators from viewing data in all databases.
My article is a complement to David Barbarin’s article ‘SQL Server 2014: SELECT ALL USERS SECURABLES & DB admins’.
I have tested some cases to be sure that I can do my DBA’s job as well.
As a reminder, msdb database is used by the SQL agent for alerts and jobs. It also contains the backup history, SSIS packages like Maintenance Plans, and more useful information.
In conclusion, we can see that this database is very important for daily jobs of DBAs.
To begin, let’s configure a restricted dba user.
Step 1: Create a role with CONTROL SERVER permission
USE [master] GO CREATE SERVER ROLE [restrictedadmin] AUTHORIZATION [DBIDBA_GROUP] GO GRANT CONTROL SERVER TO [restrictedadmin]DENY SELECT ALL USER SECURABLES TO [restrictedadmin] GO
Step 2: Add the DBA group with the restricted administration to the server
CREATE LOGIN [DBIRestricted_DBA_GROUP] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO
Step 3: Add the restricted DBA group to the server role [restrictedadmin]
ALTER SERVER ROLE [restrictedadmin] ADD MEMBER [DBIRestricted_DBA_GROUP] GO
Now, I have a Restricted_DBA_GROUP group featuring the CONTROL SERVER permission only and without the right to view data in all databases by using the new server permission DENY SELECT ALL USER SECURABLES.
Check msdb views
The first step consists of accessing some basic msdb tables like backupset or sysalerts.
I just begin with a simple query: select * from msdb.dbo.backupset ….
The Select is denied… OK, maybe it just concerns this table!
Then I test it with other useful msdb tables like sysalerts, sysjobhistory, sysoperators, sysschedules, and sysssispackages.
The Select is denied for all views. Oops!
Do I experience the same with others system views in msdb?
No, I don’t and if I run the same on the user database, the queries run successfully.
The main question is why?
In my test, I was able to show that I can access the sys and INFORMATION_SCHEMA schemas which are reserved for system objects and metadata.
But I haven’t permissions to access the dbo schema in msdb. At this point, I can understand that dbo permissions are denied to classic database users, but as a DBA, I need to access the msdb database.
Now, I will grantmy user [Restricted_DBA_GROUP] the SELECT permission on msdb tables. It’s a good idea, isn’t it?
USE [msdb] GRANT SELECT ON SCHEMA::dbo TO [Restricted_DBA_GROUP]
I repeated my test with all views and the result was the same. The select permission is still denied…
We notice that the deny permission overrules the grant permission.
It is rather reassuring 🙄
Be careful when you restrict permissions for a database administrator.
I have seen a lot of articles or demos saying that Deny the ‘SELECT ALL USER SECURABLES’ is the solution to deny viewing data in all databases.
It’s not false, but for a real database administrator who needs to use msdb objects, it does not realy apply.
For either an auditor (security auditor) or a dba junior or a trainee, it’s a good way to limit the data access.
Use this blog to convince your customer if you are a “good” DBA consultant like me…:-D