SQL Server Blog

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

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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.


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 '##%##';
Article by Jeff Iannucci
Jeff loves to help others save time. Whether improving query performance or reducing downtime for maintenance tasks or migrations, he’s not only passionate about discovering ways to save our most valuable resource, but also about sharing what he’s learned. Starting as an accidental DBA in 1998, Jeff has worked extensively with SQL Server database design, administration, and T-SQL development. He appreciates any opportunity to share his knowledge with the community, and has been presenting at user groups and writing blog posts since 2018. He was chosen as an IDERA ACE for 2020, and in 2021 became an author for Pluralsight. When not resolving database issues, he enjoys watching Indycar racing, attending Arizona Diamondbacks and Phoenix Suns games, and cooking Italian food for his wife and four children.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This