Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Verify the databases a SQL Server login can see - and why


By:   |   Last Updated: 2014-11-06   |   Comments (4)   |   Related Tips: More > Security

Problem

Security is becoming more and more of a concern these days. In some shops, the path of least resistance is to give developers system admin access to instances of SQL Server. It is often a better choice to tighten things up a little more than that - only granting access to the databases and objects that they should be able to access. As these permissions are doled out over time, though, and since permissions to databases can be inherited in different ways, it can be difficult to inventory what they can see and do.

Solution

There are several features within SQL Server that will allow you to get a good handle on the databases a specific login can see or access. And there are very easy ways to limit the databases these logins can see. Let's start by creating a handful of logins with different roles and database access (and I am going to constrain much of this discussion to SQL Authentication logins, since there are layers of additional complexity when we are talking about Windows Authentication, domain groups, and especially multiple domains):

 
USE [master];
GO

-- add "boss" to sysadmin:
CREATE LOGIN boss WITH PASSWORD = 'x', CHECK_POLICY = OFF;
ALTER SERVER ROLE sysadmin ADD MEMBER boss;

-- add "dev1" to serveradmin:
CREATE LOGIN dev1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
ALTER SERVER ROLE serveradmin ADD MEMBER dev1; 

-- add "dev2" to dbcreator:
CREATE LOGIN dev2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
ALTER SERVER ROLE dbcreator ADD MEMBER dev2; 

-- "peon1" will only be in public
CREATE LOGIN peon1 WITH PASSWORD = 'x', CHECK_POLICY = OFF;

-- "peon2" will be in public *and* granted explicit access to AW2014:
CREATE LOGIN peon2 WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE AdventureWorks2014;
GO
CREATE USER peon2 FROM LOGIN peon2;
GO

If we connect to Object Explorer in Management Studio as any of those users, we can see all the databases on the system, even those where we don't have explicit rights:

Object Explorer: All databases are visible

Seeing the name is one thing; being able to connect or access is another. Thankfully, even trying to expand the node for the CreditCardData database (for all logins above, except the one added to sysadmin) yields this error:

Object Explorer: Error message trying to access a database

A very easy way to fix the issue about seeing the name is with the VIEW ANY DATABASE permission. If we apply the following to peon1:

USE [master];
GO
DENY VIEW ANY DATABASE TO peon1;

If we refresh Object Explorer, we see that their visibility into databases on the system is cut back significantly (metadata for master and tempdb will always be visible by default):

Object Explorer: Fewer databases are visible

I think this is an important first step that is ignored when new logins are created to facilitate access to a database. It is a double-edged sword, though: it can't be used to override privileges a login may have due to server role membership (e.g. sysadmin) or, in some cases, AD group membership. And it can actually prevent visibility of databases a login should have been able to see (so it can sometimes cause extra administrative work - which is a small price to pay, in my opinion, for security - when it matters). Note that it isn't perfect (more on this later).

Being able to connect to the Object Explorer node in Management Studio isn't everything. In fact, in many cases, it's not even a valid test you can perform for SQL Authentication logins, because you may not know their password (even a sysadmin can't read a login's password). So, how can you test a login's access directly? With EXECUTE AS. This is a powerful mechanism you can use to simulate the experience a login or user will have without actually having to log in as them. Once you are impersonating a login, you can then test anything you want, because you will be running as them. In this context, you can check their access to any database - while you may be tempted to loop through all of the databases and attempt to connect or select, you can use the HAS_DBACCESS() function for a much simpler approach:

EXECUTE AS LOGIN = N'peon1';
GO
SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;
GO
REVERT;

(The call to REVERT is an important step here, otherwise you will be stuck as that other login - and it must be executed in the same database context as EXECUTE AS.)

Currently, this should yield only the following for peon1, since they have been denied VIEW ANY DATABASE:

name
------
master
tempdb

And in fact you will get the same results if you leave off the where clause, since viewing the metadata of databases where you have NOT been granted explicit access is exactly what DENY VIEW ANY DATABASE does and is exactly what HAS_DBACCESS() (and the catalog views themselves) validate. You will also find that users in the public role, by default, can access catalog views in msdb (such as msdb.sys.all_objects), but not any of the job-related tables. Even though the above query implies that access to msdb has not been granted.

You don't have to be a sysadmin to impersonate, either. You can grant this privilege to another login so that they can impersonate specific logins, or any login. (Note that all logins also inherit the right automatically with the CONTROL SERVER permission.) You need to be careful with this, though, as it can open doors for elevation of privilege. For example, let's grant impersonate on boss to peon1:

USE [master];
GO
GRANT IMPERSONATE ON LOGIN::[boss] TO [peon1];
-- or GRANT IMPERSONATE ON ANY LOGIN TO [peon1];

Then launch a new query window as peon1 and run the following code:

USE [master];
GO
SELECT TOP (1) 'Yes' FROM CreditCardData.sys.all_objects;
GO
EXECUTE AS LOGIN = 'boss';
GO
SELECT TOP (1) 'Yes' FROM CreditCardData.sys.all_objects;
GO
REVERT;

The first statement yields this error, as expected:

Msg 916, Level 14, State 1, Line 3
The server principal "peon1" is not able to access the database "CreditCardData" under the current security context.

The second statement yields "Yes" - meaning they can see the objects inside that database. Since they impersonated a sysadmin, we know they can also access any data. For other impersonations this may not be true; I just wanted to make it clear that elevation is possible.

A "Gotcha"

As an aside, DENY VIEW ANY DATABASE is not foolproof; in some scenarios this permission is bypassed entirely. For example, this script will allow even a user only in the public role to enumerate the databases on the system, without having to face any metadata validation:

EXECUTE AS LOGIN = N'peon1';
GO
;WITH v(n) AS 
(
  SELECT number FROM [master].dbo.spt_values
),
n(n) AS 
(
  SELECT TOP (32766) n = ROW_NUMBER() OVER (ORDER BY v.n)
  FROM v CROSS JOIN v AS v1 ORDER BY n
)
SELECT db = DB_NAME(n) FROM n 
WHERE DB_NAME(n) IS NOT NULL
ORDER BY db;
GO
REVERT;

This will return all of the database names on the system (at least those that are online), including CreditCardData, even though - as we've proven above - peon1 can't see that database in sys.databases or in Object Explorer. This is because the DB_NAME() function does not bother with pesky security checks (and a Connect item by Erland Sommarskog, #755720, is currently marked as "Won't Fix"). This means that any user with access to your system can see the names of all databases, which can be an issue if database names include or imply sensitive information. Please vote up Erland's Connect item and comment on why this is a critical security issue for your business. Obscurity is not security, but this seems like fundamentally unnecessary exposure.

So how does all of this information help us?

Our original goal was to validate which databases a login can see and access. Armed with the information above, we can easily generate a script to check a set of logins and see which databases they have visibility into. This is a little convoluted, but basically we need dynamic SQL to run EXECUTE AS for each login we care about, against each database on the system, and then report on the results.

-- table for collecting data
CREATE TABLE #x([login] SYSNAME, db SYSNAME, error INT);

-- table of logins we care about today
DECLARE @logins TABLE([login] SYSNAME);
INSERT @logins([login]) VALUES(N'boss'),(N'peon1'),(N'peon2');

-- build SQL commands for read attempts to each online db
DECLARE @sql NVARCHAR(MAX) = N'', @cmd NVARCHAR(MAX) = N'';

SELECT @sql += N'
 BEGIN TRY
  INSERT #x SELECT TOP (1) SUSER_SNAME(),N''' + REPLACE(name,'''','''''')
    + N''',0 FROM ' + QUOTENAME(name) + N'.sys.all_objects;
 END TRY
 BEGIN CATCH
   INSERT #x SELECT SUSER_SNAME(),N''' + REPLACE(name,'''','''''') 
     + ''', ERROR_NUMBER();
 END CATCH;' FROM sys.databases
 WHERE [state] = 0;

SELECT @cmd += N'
  EXECUTE AS LOGIN = N''' 
   + REPLACE([login], '''', '''''') + N''';
  ' + @sql + N'
  REVERT;' FROM @logins;

EXEC [master].sys.sp_executesql @cmd;

-- report
SELECT [login], [db], [Access?] = CASE error
  WHEN 0   THEN 'Yes'
  WHEN 229 THEN 'Access denied on sys.all_objects'
  WHEN 916 THEN 'Cannot connect to database'
  ELSE 'No - ' + CONVERT(VARCHAR(11), error) END
FROM #x ORDER BY [login],db;
GO

--DROP TABLE #x;

Results:

Result of our check againsgt all online databases

(Note that I first added peon1 as a user in master, and ran DENY SELECT ON sys.all_objects TO peon1;. Also note that this doesn't mean they can't access *other* objects in master, only that they've been explicitly denied access to that view (which should be uncommon).)

Note that this tells us they can query sys.all_objects - we already know they can access the name of any database on the system using the hole in DB_NAME(). For anything they can access that you don't think they should, you can go back through their server role membership, database ownership, database role membership and granted privileges to see why they can access that object in that database (and really you could change any example on this page to look at other tables or views). So given a login (say, peon1) and a database (say, AdventureWorks2014), you could review the results of these queries to try to backtrace how they got access:

DECLARE @login SYSNAME = N'peon1', @db SYSNAME = N'AdventureWorks2014';

-- all server roles they are a member of
SELECT [server role] = r.name, role_type = r.type_desc
    FROM sys.server_principals AS p
    INNER JOIN sys.server_role_members AS rm
    ON p.principal_id = rm.member_principal_id
    INNER JOIN sys.server_principals AS r
    ON rm.role_principal_id = r.principal_id
    WHERE p.name = @login;
    
-- all explicit server permissions they have been granted
SELECT perm.[permission_name], perm.state_desc, perm.class_desc
    FROM sys.server_permissions AS perm
    INNER JOIN sys.server_principals AS sp
    ON perm.grantee_principal_id = sp.principal_id
    WHERE sp.name = @login;

-- do they own this db?
SELECT [owned_database] = name
    FROM sys.databases
    WHERE owner_sid = SUSER_SID(@login)
    AND name = @db;

-- dynamic SQL so only have to name database once

-- are they a user in @db? If so, what roles are they in?
DECLARE @exec NVARCHAR(300) = @db + N'.sys.sp_executesql',
  @sql NVARCHAR(MAX) = N'SELECT 
      [user] = p.name, [db role] = r.name, role_type = r.type_desc
    FROM sys.database_principals AS p
    INNER JOIN sys.server_principals AS sp
    ON p.[sid] = sp.[sid]
    LEFT OUTER JOIN sys.database_role_members AS rm
    ON p.principal_id = rm.member_principal_id
    LEFT OUTER JOIN sys.database_principals AS r
    ON rm.role_principal_id = r.principal_id
    WHERE sp.[sid] = SUSER_SID(@login);';

-- do they have explicit permissions on sys.all_objects?
SET @sql += N'SELECT perm.state_desc, perm.[permission_name]
    FROM sys.database_permissions AS perm
    INNER JOIN sys.all_views AS v
    ON perm.major_id = v.[object_id]
    INNER JOIN sys.database_principals AS dp
    ON perm.grantee_principal_id = dp.principal_id
    INNER JOIN sys.server_principals AS sp
    ON dp.[sid] = sp.[sid]
    WHERE v.name = N''all_objects'' AND v.[schema_id] = 4
    AND sp.[sid] = SUSER_SID(@login);';

EXEC @exec @sql, N'@login SYSNAME', @login;

I'll leave parsing and interpreting the results as an exercise for the reader (and let me know below if you think there are other things we should check - with the noted exception of Windows logins and AD group membership). You should be aware that this script only detects direct role membership; it does not perform recursive cycles to find nested roles (see this tip for more info on nested roles).

Next Time...

...we'll go a little deeper, and see how we can use similar logic to see what things a user can do inside a database.

Security is an extremely vast topic, and the above is just the tip of the iceberg. I hope this gives you a straightforward way to verify how a specific SQL Authentication login has access to a specific database, and opens the door for further exploration.

Next Steps


Last Updated: 2014-11-06


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, February 05, 2015 - 9:11:25 AM - Larry Smith Back To Top

Aaron, this is very enlightening and helpful.

 

Although setting DENY for Active Directory security groups responds as successful, scripts provided to list what the user will see are failing.  Do you have alternative scripts for this?


Thursday, February 05, 2015 - 1:45:39 AM - Jefferson Elias Back To Top

Hi, do you know that I'm currently developping a solution to implement a standard security ?

http://www.sqlservercentral.com/articles/Generator/120353/

It helps you define and document the security to apply for each login and then generate a T-SQL Script you can execute against the server for which you defined the security model.


Thursday, November 06, 2014 - 5:34:43 AM - shashi Back To Top

sir i wnna to become sqlserver dba 

plz inform wt r prerequeties for dba 

thank you 


Thursday, November 06, 2014 - 4:37:00 AM - Thomas Franz Back To Top

Very nice article about a topic I just spoke yesterday with my colleague :-)

Would it be a good idea to remove the VIEW ALL DATABASES privilege from the public role (not deny, just remove), create roles for each application / security scenario etc. and grant access to the particular database(s) to the role?

This way nobody (except the SysAdmin) should be able to see any user database except the ones that are assigned to their roles (even if a new user account was created and nobody thought about denying access).

 


Learn more about SQL Server tools