Validating sql privileges
(Note that all logins also inherit the right automatically with the 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.
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 .
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).
This is a little convoluted, but basically we need dynamic SQL to run -- 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'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]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;.
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 Adventure Works2014; GO CREATE USER peon2 FROM LOGIN peon2; GO Seeing the name is one thing; being able to connect or access is another.
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).
In the results of the query, you'll see execute listed but with no specific object type or object name!
Security is becoming more and more of a concern these days.
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]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; 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.