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 controlserverlogin
GODENY VIEW ANY DATABASE TO sysadminlogin
GO
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 and execute 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';GO
REVERT;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.
If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba
Below script list the logins with sysadmin role membership and control server permission.
/* List login with membership to Sysadmin server role and Control Server right*/SELECT p.[name] [Login], 'sysadmin Role Member' [Access] ,p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_role_members RM
ON p.principal_id = rm.member_principal_id
JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id WHERE rp.NAME = 'sysadmin' UNION ALL SELECT p.[name], 'Control Server Right' ,p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' GOBelow script will help to list the explicitly denied server level permission for logins which have control server permission.
/* List Explicitly denied permission for the login that has control server permission*/SELECT CSL.*,sp.permission_name [Explicitly Denied Permission] FROM sys.server_permissions SP INNER JOIN (SELECT p.principal_id ,p.[name], p.type_desc AS loginTypeFROM sys.server_principals p
JOIN sys.server_permissions Sp
ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
AND sp.[type] = 'CL'
AND state = 'G' ) CSL ON CSL.principal_id = sp.grantee_principal_id
WHERE state ='D'
If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba
www.PracticalSqlDba.com
