Yesterday I blogged about how to figure out what database principals corresponded to what server principals. The key is to match up the SIDs between sys.server_principals and sys.database_principals. But I also stated there were 3 cases where the logins had implicit access to a database and therefore we wouldn't see a mapping. Those cases were:

• The login is a member of the sysadmin fixed server role. In this case the login comes in as dbo on all databases and you won't see a mapping in sysusers/sys.database_principals.

• The login has CONTROL SERVER rights (SQL Server 2005 and above). In this case, the login functions as above on all databases.

• The login owns the database. In this case the login comes in as dbo for that particular database and you won't see a mapping in sysusers/sys.database_principals. * See explanation below.

Let's take a look at each case and look at how to detect those logins.

SysAdmin Fixed Server Role:

 The easiest way to detect members of the sysadmin fixed server role is to use the system stored procedure sp_helpsrvrolemember like so:

EXEC sys.sp_helpsrvrolemember 'sysadmin';


You can also use the security catalog views in SQL Server 2005 and 2008 to query for the same information. This is useful if you're trying to build one big query to return all three cases. Then you end up with something similar to:

SELECT sp1.[name] [Login]
FROM sys.server_principals sp1
  JOIN sys.server_role_members srm
    ON sp1.principal_id = srm.member_principal_id
  JOIN sys.server_principals sp2
    ON srm.role_principal_id = sp2.principal_id
WHERE sp2.NAME = 'sysadmin'

CONTROL SERVER Permission:

CONTROL SERVER is a new permission as of SQL Server 2005 and it grants the same access as being a member of the sysadmin fixed server role. If I'm an attacker and I want to keep hidden the fact that I have complete control of the SQL Server, I'm going to use CONTROL SERVER. That's because most people still only audit for membership in the sysadmin fixed server role. However, finding out if any logins have this permission is as easy as query sys.server_permissions. When doing so, there are a couple of things to keep in mind:

• For permissions against the server, class = 100.

• For the CONTROL SERVER permission, type = 'GL'

• To signify the permission has been granted, not denied, state = 'G'

Knowing those facts, it's easy to build a query like the following to return who has such rights on the server:

SELECT sp.[name]
FROM sys.server_principals sp
  JOIN sys.server_permissions perm 
    ON sp.principal_id = perm.grantee_principal_id
WHERE perm.class = 100
  AND perm.[type] = 'CL'
  AND state = 'G'

Database Owners:

The last scenario is if the login is the owner of the database. In this case the login maps as dbo. Now if you do query sys.database_principals, you will see a match; it'll just be as dbo. So it does map, and I stated it incorrectly in my previous blog post (which has been edited to point out that it does). If you still want to query on who maps into the databases as dbo, you can do so without having to check each and every database by using sys.databases:

SELECT sd.[name] [Database], sp.[name] [Login]
FROM sys.databases sd
  JOIN sys.server_principals sp
    ON sd.owner_sid = sp.sid

Putting the First Two Scenarios Together:

Since the last scenario was a mistake on my part, let's focus on putting together the first two cases. A simple UNION ALL will allow us to combine the queries, along with

SELECT sp1.[name] [Login], 'sysadmin role' [Method]
FROM sys.server_principals sp1
  JOIN sys.server_role_members srm
   ON sp1.principal_id = srm.member_principal_id
  JOIN sys.server_principals sp2
   ON srm.role_principal_id = sp2.principal_id
WHERE sp2.NAME = 'sysadmin'

UNION ALL

SELECT sp3.[name], 'CONTROL SERVER'
FROM sys.server_principals sp3
  JOIN sys.server_permissions perm 
   ON sp3.principal_id = perm.grantee_principal_id
WHERE perm.class = 100
  AND perm.[type] = 'CL'
  AND state = 'G'

ORDER BY [Login], [Method];