By: Aaron Bertrand | Updated: 2014-11-06 | Comments (4) | Related: > 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:
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:
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):
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:
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:
(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
- Bookmark this page, in case you need quick access to these code samples in the future.
- Review the following tips and other resources:
- Using Windows Groups for SQL Server Logins as a Best Practice
- Issues Determining an Individual SQL Server User's Permissions
- Implicit Permissions Due to SQL Server Database Roles
- Understanding How A User Gets Database Access in SQL Server
- Potential Security Exploit Using CONTROL SERVER Permissions in SQL Server
- All Security tips
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist 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 also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-11-06
