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

GO

CREATE USER peon2 FROM LOGIN peon2;

GO

USE [master];

GO

DENY VIEW ANY DATABASE TO peon1;

EXECUTE AS LOGIN = N'peon1';

GO

SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;

GO

REVERT;

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