Understanding How A User Gets Database Access in SQL Server

By:   |   Comments (8)   |   Related: > Security


Problem

I have been tasked with auditing security on my SQL Server. I understand that logins allow you to connect to SQL Server, but I'm not quite understanding how to determine whether a login has access to a database or not. For instance, I know that all logins can access the master database, but when I look at a login in SQL Server Management Studio, I don't see a checkbox beside the master db for that login. How can I determine what databases a login has access to?

Solution

You're right, that if you look in SQL Server Management Studio you won't see a login's access to the master database unless the DBA has done something explicitly. An example is shown in Figure 1.

Figure 1:

SQL Server Management Studio

But we do know that all logins can access the master database. So let's talk about how a login can connect to a given database. There are five possible ways a login can have permission to connect to a given database:

  1. Explicit access is granted.
  2. The login is a member of the sysadmin fixed server role.
  3. The login has CONTROL SERVER permissions (SQL Server 2005/2008 only).
  4. The login is the owner of the database.
  5. The guest user is enabled on the database.

Explicit Access (Login Mapped to Database User):

The first way is if a login is given explicit access to a database. For instance, in SQL Server 2000, if I had a user MyTestUser, I would grant access like so from within the database:

EXEC sp_grantdbaccess 'MyTestUser';
GO
 

In SQL Server 2005 and 2008 there are new T-SQL commands to create logins and users. So I would use the following command to do the same thing:

CREATE USER MyTestUser FOR LOGIN MyTestUser;
GO
 

A login granted access in this manner should appear in the sysusers table (SQL Server 2000) or the sys.database_principals catalog view (SQL Server 2005/2008). For instance, here's how I would match up users in a given database to their corresponding logins (SQL Server 2000):

SELECT sl.name AS 'Login'su.name AS 'User' 
FROM master..syslogins sl
  
JOIN sysusers su
    
ON sl.sid su.sid
ORDER BY sl.namesu.name;
 

And here's how we'd do it in SQL Server 2005/2008:

SELECT sp.name AS 'Login'dp.name AS 'User'
FROM sys.database_principals dp
  
JOIN sys.server_principals sp
    
ON dp.sid sp.sid
ORDER BY sp.namedp.name;

If you see a login match up to a user in this manner, then the login has access to the database.


Implicit Access (Member of Sysadmin Fixed Server Role):

All members of the sysadmin fixed server role map to the dbo user of every database. Therefore, if a login is a member of this role, it automatically has access to every database.

Here is the query for SQL 2000 to see members of the sysadmin fixed server role.

EXEC sp_helpsrvrolemember 'sysadmin'

Here is the query for SQL 2005/2008 to see members of the sysadmin fixed server role.

SELECT sp.name
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp
     
ON srm.member_principal_id sp.principal_id
WHERE srm.role_principal_id (
     
SELECT principal_id
     
FROM sys.server_principals
     
WHERE [Name] 'sysadmin')

Implicit Access (CONTROL SERVER permission - SQL Server 2005/2008):

The CONTROL SERVER permission gives equivalent rights as a member of the sysadmin role with a few exceptions, which aren't of importance here. Therefore, if a login doesn't map explicitly to a user in a database, but that login has CONTROL SERVER permissions, that login can still access the database. You can see who has CONTROL SERVER permissions by the following query:

SELECT sp.name 'Login' 
FROM sys.server_principals sp
   
JOIN sys.server_permissions perms
     
ON sp.principal_id perms.grantee_principal_id
WHERE perms.type 'CL'     
  
AND perms.state 'G';

Implicit Access (Database Owner):

The database owner automatically maps into the database as the dbo user. The query given under explicit access should reveal the owner by just looking at the dbo user. However, another way is to query the sysdatabases table (SQL Server 2000) or sys.databases catalog view (SQL Server 2005/2008). Here's the SQL Server 2000 query that reveals all the owners of all the databases on the server:

SELECT db.name AS 'Database'sl.name AS 'Owner' 
FROM sysdatabases db
  
INNER JOIN syslogins sl
    
ON db.sid sl.sid
ORDER BY db.name;

And here's how to do the same thing in SQL Server 2005/2008:

SELECT db.name AS 'Database'sp.name AS 'Owner'
FROM sys.databases db 
  
LEFT JOIN sys.server_principals sp
    
ON db.owner_sid sp.sid
ORDER BY db.name;

Implicit Access (Guest User Is Enabled):

The final way a login can get access to a database is if the guest user is enabled for that database. If a login cannot map in any other way, it'll use guest if that's available. That's actually how logins can access the master database. The guest user is enabled. With respect to user databases, the guest user should only be enabled in special cases. The default is for it to be disabled. However, there are two system databases which the guest user must always remain enabled. They are:

  • master
  • tempdb

And that explains why logins always have access to master, even when explicit rights aren't visible. To see if the guest user is enabled we can query sysusers (SQL Server 2000) or sys.database_permissions (SQL Server 2005/2008). Here's how to do it in SQL Server 2000:

SELECT su.nameCASE su.hasdbaccess WHEN THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sysusers su
WHERE su.name 'guest';

In SQL Server 2005/2008 we have to look for the existence of the CONNECT permission at the database level for the guest user. If it exists, the guest user is enabled. If it doesn't, then the guest user is not.

SELECT dp.nameCASE perms.class WHEN THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
  
LEFT JOIN (SELECT grantee_principal_idclass FROM sys.database_permissions 
              
WHERE class AND type 'CO' AND state 'G'AS perms
    
ON dp.principal_id perms.grantee_principal_id
WHERE dp.name 'guest'
Next Steps
  • Take the time to audit your servers for any security holes.  Use these queries to identify any possibnle issues.
  • Refer to these other tips related to security


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, August 30, 2013 - 4:21:57 AM - HTCOneSUser Back To Top (26543)

Ok, especially I am interested in this (http://www.mssqltips.com/sqlservertip/1370/identifying-deprecated-sql-server-code-with-profiler/) topic coverage ( in this context ) :)


Thursday, August 29, 2013 - 11:16:51 AM - K. Brian Kelley Back To Top (26531)

I do need to update for SQL Server 2012, especially with contained DBs. That's on my list.


Thursday, August 29, 2013 - 8:14:51 AM - HTCOneSUser Back To Top (26524)

Any update of this article for SQL Server 2012 specifics?

 

Thanks again for this one. It helped me a lot in recent developments. ;)


Wednesday, April 10, 2013 - 8:06:51 AM - Pankaj Deswal Back To Top (23270)

Hi Brian, I want to authorize mysql databse From CAS server. Actually we are building a web application which is having authentication from CAS server. I want that only logged user can access mysql database. Before sending the mysql query to databse I want it to check from CAs server that the user is having CAS token is valid logged user or not. Any user that is not logged from CAs server can't access to databse. Please give suggesstion on this.


Thursday, May 12, 2011 - 5:02:15 PM - Jeremy Kadlec Back To Top (13818)

David and Brian,

The code from the "Implicit Access (Database Owner)" section of the tip for SQL Server 2005 and 2008 has been updated.

Thank you,
Jeremy Kadlec


Monday, May 9, 2011 - 9:33:55 AM - K. Brian Kelley Back To Top (13781)

Hi David,

  that works, but I think it reveals a weakness in the configuration environment. We typically ensure that all databases are owned by a non-user account. That way if the user leaves the organization, there aren't any issues. This can be accomplished using policy-based management, for instance. However, by seeing the fact that the database owner is NULL, that should alert someone to check into why. I'll ask the folks here to update the script on the page accordingly.


Friday, May 6, 2011 - 9:48:31 AM - David Wagoner Back To Top (13768)

Brian,

I enjoyed reading your article and exploring the queries that you provided. 

While executing the Database Owner query on my system, I noticed that some of the databases were not listed in the results!  The reason is that my login is a member of a Windows Security Group login (security group for DBA team members) and my login is the database owner on several databases.  Therefore, my login is not explicitly listed in sys.server_principals so it is not returned by the INNER JOIN used in your query.  I modified the query to use LEFT JOIN and now all databases are returned with NULL owner values where the owner login is not listed in sys.server_principals.  Those databases would require further investigation to determine the owner.

SELECT db.name AS 'Database', sp.name AS 'Owner'
FROM sys.databases db
	LEFT JOIN sys.server_principals sp 
		ON db.owner_sid = sp.sid
ORDER BY db.name;

Wednesday, April 27, 2011 - 10:17:23 AM - David Taylor Back To Top (13698)

Brian,

Thanks for this! The area I am absolutely weakest in is security, and tips like these help me immensely to learn the ins and outs. Keep them coming!















get free sql tips
agree to terms