Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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