Quick SQL Instance and DB Access Collection
/* We collect the results daily and haven't been too concerned | |
about lost history if a particular instance fails occasionally */ | |
DELETE FROM | |
dbo.secPoll; | |
CREATE TABLE ##userQuery | |
( | |
serverName VARCHAR(255) | |
, databaseName VARCHAR(255) | |
, databaseRoleName VARCHAR(255) | |
, memberName VARCHAR(255) | |
, loginType VARCHAR(50) | |
, status VARCHAR(50) | |
); | |
DECLARE @theTime DATETIME2(0) = GETDATE(); | |
/* dynamic SQL for collecting DB permissions for each instance using Aaron Bertrands sp_foreachdb | |
but you'd be better off incorporating the new procedure: | |
https://sqlblog.org/2020/08/04/reliable-flexible-replacement-sp_msforeachdb */ | |
EXEC master.dbo.sp_foreachdb @command = N'USE[?]; | |
INSERT INTO ##userQuery (serverName | |
, databaseName | |
, databaseRoleName | |
, memberName | |
, loginType | |
, status) | |
SELECT @@SERVERNAME AS serverName, DB_NAME() AS databaseName | |
, DP1.name AS DatabaseRoleName | |
, ISNULL(DP2.name, ''No members'') AS MemberName | |
, CASE DP2.type | |
WHEN ''U'' THEN ''WinLogin'' | |
WHEN ''S'' THEN ''SQLLogin'' | |
WHEN ''G'' THEN ''WinGroupLogin'' | |
ELSE ''UNKNOWN'' | |
END AS LoginType | |
, CASE WHEN l.name IS NULL THEN ''Orphan'' ELSE ''N/A'' END AS [status] | |
FROM | |
[?].sys.database_role_members AS DRM | |
RIGHT OUTER JOIN [?].sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id | |
LEFT OUTER JOIN [?].sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id | |
LEFT OUTER JOIN master.sys.syslogins l ON l.sid = DP2.sid | |
WHERE | |
DP1.type = ''R'' | |
AND DP2.name IS NOT NULL | |
AND DP2.name <> ''dbo'' | |
AND DP2.type IN (''G'', ''U'', ''S'')' | |
, @exclude_list = N'tempdb,model' | |
, @print_dbname = 1; | |
/* union all the db permissions with the instance permissions | |
and stuff it into the local table */ | |
INSERT INTO dbo.secPoll (serverName | |
, databaseName | |
, databaseRoleName | |
, memberName | |
, loginType | |
, status | |
, dateDetected) | |
SELECT serverName | |
, databaseName | |
, databaseRoleName | |
, memberName | |
, loginType | |
, status | |
, @theTime AS dateDetected | |
FROM ##userQuery | |
WHERE memberName NOT LIKE '##%' | |
UNION | |
SELECT @@SERVERNAME AS serverName | |
, 'N/A' AS databaseName | |
, CASE WHEN b.sysadmin = 1 THEN | |
'sysadmin' | |
WHEN b.securityadmin = 1 THEN | |
'securityadmin' | |
WHEN b.serveradmin = 1 THEN | |
'serveradmin' | |
WHEN b.setupadmin = 1 THEN | |
'setupadmin' | |
WHEN b.processadmin = 1 THEN | |
'processadmin' | |
WHEN b.diskadmin = 1 THEN | |
'diskadmin' | |
WHEN b.dbcreator = 1 THEN | |
'dbcreator' | |
WHEN b.bulkadmin = 1 THEN | |
'bulkadmin' | |
ELSE | |
'Public' | |
END AS ServerRole | |
, a.name AS MemberName | |
, CASE WHEN a.type_desc = 'SQL_LOGIN' THEN | |
'SQLLogin' | |
WHEN a.type_desc = 'WINDOWS_GROUP' THEN | |
'WinGroupLogin' | |
ELSE | |
'WinLogin' | |
END AS LoginType | |
, CASE WHEN a.is_disabled = 1 THEN | |
'Disabled' | |
WHEN a.is_disabled = 0 THEN | |
'Enabled' | |
WHEN a.is_disabled IS NULL THEN | |
'Error' | |
END AS Status | |
, @theTime AS dateDetected | |
FROM sys.server_principals AS a | |
JOIN master.dbo.syslogins AS b ON a.sid = b.sid | |
WHERE a.name NOT LIKE '##%' | |
ORDER BY serverName | |
, memberName; | |
DROP TABLE ##userQuery; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment