Caveats of a CONTROL SERVER permission

2012/03/29

Today morning I was working on one of the server to complete the regular documentation of security audit with details of logins with sysadmin rights and their access to various database. Later I have realized that, one login has access to all database but it is not captured by my auditing script. Here comes the culprit , the CONTROL SERVER rights.

By default CONTROL SERVER rights is equivalent to sysadmin permission except the fact that logins with CONTROL SERVER rights will honor the explicitly denied server level permission where the members of sysadmin server role bypass the explicitly denied server level permission. Also note that logins with control server permission will have implicit access to the databases like the sysadmin members and database owners.

Logins with control server rights will not have mapping entry in the sys.database_principals but it will have access to all databases. The worst part is, logins with CONROL SERVER permission are not easy to find out unless you prepare explicit query. It is not listed in the UI of SSMS or there is no system procedure like sp_helpsrvrolemember to list the logins with CONTROL SERVER right.

Let us walk through a sample script. Create two logins using the below script

CREATE login SysadminLogin WITH password ='password123~'GOCREATE login controlserverlogin WITH password ='password123~'GOEXEC sp_addsrvrolemember 'SysadminLogin','sysadmin'GOGRANT control server TO controlserverlogin Now log in to the server using the controlserverlogin and you can access all the databases and perform any actions. Let us see what will happen on explicitly denying the server level permission.

DENY VIEW ANY DATABASE TO controlserverloginGO

DENY VIEW ANY DATABASE TO sysadminloginGO

Now log in to the server using both the login. You can notice that, in the session that connected with the controlserverlogin will list only Master and Tempdb databases while the session connected with sysadminlogin will list all available databases.

The other potential issue with logins having control server right is , they can add them self to the sysadmin server role or can create a new login with membership to the sysadmin server role. Fortunately it is not possible to do it in straight forward steps. Let us see how it will work .Connect to the server using the controlserverlogin andexecute the below scripts

/* Fortunately this will fail */ EXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';GO/* Unfortunately this will work even if sa account is disabled*/EXECUTE AS LOGIN = 'sa';GOEXEC sp_addsrvrolemember 'controlserverlogin','sysadmin';GOREVERT;

Now the controlserverlogin has sysadmin role membership and you can see all available databases.

Below script list the logins with sysadmin role membership and control server permission.