As I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

SECURITY question

The next question in the list is:

What is the difference between sysadmin and CONTROL SERVER Permission?

At a high level, the difference between sysadmin and CONTROL SERVER is the ability to restrict permissions on CONTROL SERVER.  As has been described to me, most security checks in SQL Server are designed to pass the security check if the principal is a member of sysadmin.  With all other permissions, CONTROL SERVER included, the security check will validate whether or not there is sufficient permissions assigned to the principal to access that portion of SQL Server.

The CONTROL SERVER securable has been designed as a long-term replacement for the sysadmin role.  One of the key needs in today’s environments is to be able to lock down access for users and your DBAs.  This wasn’t entirely possible before the inclusion of the CONTROL SERVER securable.  Or rather it wasn’t possible to lock down DBAs and still provide them with the access they required to be able to do their jobs.

Conceptually, CONTROL SERVER is supposed to be everything that sysadmin is but with the flexibility for denying access as needed.  Unfortunately, there are a few holes in that concept.  The issue isn’t that CONTROL SERVER lacks the same permissions as sysadmin, but that there are places in code where the security logic looks specifically for sysadmin.  K. Brian Kelly (Blog | @kbriankelley) talks about this issue in his blog post “Why CONTROL SERVER Doesn’t Cut It“.

Controlling Your Junior DBAs

Outside of the issues that the other blog post, let’s look at why using this securable can be quite beneficial.  As mentioned, you are able to assign someone to CONTROL SERVER and then remove permissions on other objects from them.  We’ll test this with a new user, named JuniorDBA.  For this user, we’ll assign the CONTROL SERVER securable, as shown in Listing 1.

--Listing 1. Create JuniorDBA login
USE [master]
GO
CREATE LOGIN [JuniorDBA] WITH PASSWORD=N'pass@word1'
, DEFAULT_DATABASE=[master]
, CHECK_EXPIRATION=ON
, CHECK_POLICY=ON
GO
GRANT CONTROL SERVER TO [JuniorDBA]
GO

Login to another query window using the JuniorDBA user and run the script in Listing 2.  This query returns a list of all of the databases on the SQL Server instance.  As you can see, in Figure 1, the query returns information on all of the databases on the server.

--Listing 2. Query sys.databases as JuniorDBA
SELECT * FROM sys.databases
GO

Figure 1. Sys.databases output with full permissions

The usefulness of CONTROL SERVER comes with the ability to strip away permissions.  CONTROL SERVER grants access to everything.  For a junior DBA, we might need to remove the ability to view databases on a SQL Server instance.  To remove the permission, execute the DENY script provided in Listing 3.  This may not be a quite a real-world example, but it is an easy demonstration of what is possible with this role.  If the query from Listing 2 is run again, the results are quite changed with only rows for the master and tempdb databases included, shown in Figure 2.

--Listing 3. Create new login
DENY VIEW ANY DATABASE TO [JuniorDBA]
GO

Figure 2. Sys.databases output with limited permissions

Summary

The CONTROL SERVER has the potential to be a great addition to managing permissions for SQL Server.  There are some definite holes in using the securable, such as needing securityadmin role to view the error log, but even with these limitations there is a place for this permission within the DBA team.  Do you think you could leverage CONTROL SERVER for a junior DBA role?  How have you gotten around some of the short-comings for this role?