http://www.sqlservercentral.com/blogs/brian_kelley/2009/02/23/detecting-when-a-login-has-implicit-access-to-a-database/

Printed 2014/04/23 08:51PM

Detecting When a Login Has Implicit Access to a Database

2009/02/23

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:

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:

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];


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.