CONTROL SERVER Permissions Are the Hidden SQL Server Superpower [30 SQL Server Security Checks in 30 Days]

The CONTROL SERVER permission has been around since SQL Server 2005, and is the most powerful permission granted as part of membership in the sysadmin role. What many folks don’t realize is that this permission can be granted to a login or group without including them in the sysadmin role. And that can become problematic if, as an administrator, you aren’t aware of logins or groups that don’t have this permission.

CONTROL SERVER is almost like “god mode” in that a login with this permission can do any of the following (and more):

• Can view, drop, and create objects in any database
• Can create backups
• Can change other users permissions
• Can shutdown SQL Server
• Can alter settings (like xp_cmdshell)

However, it can’t do everything, as it is not allowed to do any of these…

• Execute many system stored procedures
• Execute most DBCC commands
• Set up Database Mail

…BUT (and this is a very big BUT) by default a login with CONTROL SERVER permissions can do one supremely powerful thing: Logins with CONTROL SERVER permission can impersonate any Login, including any member of the sysadmin role and even the sa login. All that is needed to impersonate is a little bit of T-SQL like this.

EXECUTE AS LOGIN = 'sa'

After that, a login with CONTROL SERVER permissions can do pretty much anything. Logins with these permissions are a prime target for hackers, as this ability to impersonate any member of the sysadmin role will allow for the escalation of privileges for malicious purposes like stealing data with backups, installing malware, and even removing evidence of a breach by deleting SQL Server log files.

In my experience, I’ve found most logins with CONTROL SERVER permissions to be related to some application. Perhaps the application developer didn’t want to take the time to determine the granular permissions that were needed, and simply assigned this permission to cover all possible permissions to the login used by the application. Admittedly, I’m just speculating, but I also haven’t seen many applications that need to stop SQL Server or manage the security of other logins. CONTROL SERVER seems like a bit of overkill most of the time.

So what do you do if you find any principals with CONTROL SERVER permissions? Remember, this permission isn’t exactly the same as being a member of the sysadmin role, which will allow you to contain the potential for impersonation. While a member of the sysadmin role cannot be denied any permissions, any logins with CONTROL SERVER that are not members of the sysadmin role can have permissions restricted by using the DENY command.

If you must have any logins with CONTROL SERVER permissions, we recommend that you explicitly DENY them the ability to impersonate any member of the sysadmin role. You can do this with the following T=SQL, replacing YourControlServerLogin with the name of your login.

USE master;

DENY IMPERSONATE ON LOGIN::sa TO [YourControlServerLogin];

Now, I’m sure you can see there is a potential for this to be a headache if you have a lot of members of the sysadmin role beyond the sa login, as this DENY command will need to be run for every member of the role. I’m sorry, please don’t shoot the messenger.

If you want to proceed with denying the ability to impersonate members of the sysadmin role, be sure you have some mechanism in place to deny this impersonation any time you add a new member of sysadmin role. Conversely, you may feel it’s safe to add any login with CONTROL SERVER permissions to the sysadmin role and be done with it. Or, you may want to remove the CONTROL SERVER permission and assign more granular permissions instead. It’s a tricky choice, but that’s why they pay you database administrators the big bucks.

Check your instances today for any principals with CONTROL SERVER permissions with the following T-SQL:

SELECT pri.[name]
FROM sys.server_principals AS pri
WHERE pri.[principal_id] IN (
	SELECT p.[grantee_principal_id]
	FROM sys.server_permissions AS p
	WHERE p.[state] IN ( 'G', 'W' )
	AND p.[class] = 100
	AND p.[type] = 'CL' )
    AND pri.[name] NOT LIKE '##%##';