Last active
January 22, 2024 21:36
-
-
Save petesql/1ff590618456b21b9f814c00aac05120 to your computer and use it in GitHub Desktop.
SQL Server User & Permissions Audit Script
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL Server User & Permissions Audit Script | |
USE master | |
GO | |
DECLARE | |
@errorMessage nvarchar(4000), | |
@errorNumber int, | |
@errorSeverity int, | |
@errorState int, | |
@errorLine int, | |
@include_database_without_access bit = 0; -- <---- include/remove MISSING db list | |
BEGIN TRY | |
EXEC AS LOGIN = 'sqllogin' -- or 'DOMAIN\loginname' | |
IF OBJECT_ID('tempdb..##myuser') IS NULL | |
SELECT DISTINCT @@SERVERNAME '@@servername', DB_NAME() AS dbName, | |
principal_id, sid, name, type, usage | |
INTO ##myuser | |
FROM sys.user_token WHERE sid IN (SELECT sid FROM sys.database_principals AS dp) | |
AND 1 = 2; | |
ELSE | |
TRUNCATE TABLE ##myuser; | |
INSERT INTO ##myuser | |
EXECUTE sp_msforeachdb 'Use [?]; | |
SELECT DISTINCT @@SERVERNAME as [@@servername], DB_NAME() AS dbName, | |
principal_id, sid, name, type, usage | |
FROM sys.user_token WHERE sid IN (SELECT sid FROM sys.database_principals AS dp) | |
AND name != ''public'';' | |
IF @include_database_without_access = 1 | |
BEGIN | |
INSERT INTO ##myuser | |
SELECT DISTINCT @@SERVERNAME as [@@servername], d.name as dbName, NULL AS principal_id, | |
NULL AS sid, CASE WHEN d.state_desc = 'ONLINE' THEN 'MISSING' ELSE d.state_desc END AS name, | |
'ROLE' AS type, 'MISSING' AS usage | |
FROM sys.databases AS d | |
WHERE name NOT IN (SELECT dbName FROM ##myuser AS m); | |
END; | |
SELECT DISTINCT @@SERVERNAME '@@servername', '[CONNECTION]' AS dbName, | |
type AS [ROLE], | |
CASE WHEN type = 'WINDOWS GROUP' THEN name ELSE '' END AS [WINDOWS GROUP], | |
CASE WHEN type != 'WINDOWS GROUP' THEN name ELSE '' END AS [SQL USER] | |
FROM sys.login_token WHERE sid IN (SELECT sid FROM sys.server_principals) | |
UNION ALL | |
SELECT [@@servername], dbName, | |
ISNULL(SUBSTRING( | |
(SELECT ',' + t.Name + '' | |
FROM ##myuser t | |
WHERE type = 'ROLE' | |
AND x.[@@servername] = t.[@@servername] | |
AND x.dbName = t.dbName | |
ORDER BY t.Name | |
FOR XML PATH('')), 2, 200000), '') | |
AS [ROLE], | |
ISNULL(SUBSTRING( | |
(SELECT ',' + t.Name + '' | |
FROM ##myuser t | |
WHERE type = 'WINDOWS GROUP' | |
AND x.[@@servername] = t.[@@servername] | |
AND x.dbName = t.dbName | |
ORDER BY t.Name | |
FOR XML PATH('')), 2, 200000), '') | |
AS [WINDOWS GROUP], | |
ISNULL(SUBSTRING( | |
(SELECT ',' + t.Name + '' | |
FROM ##myuser t | |
WHERE type = 'SQL USER' | |
AND x.[@@servername] = t.[@@servername] | |
AND x.dbName = t.dbName | |
ORDER BY t.Name | |
FOR XML PATH('')), 2, 200000), '') | |
AS [SQL USER] | |
FROM ##myuser x | |
GROUP BY [@@servername], dbName | |
ORDER BY [@@servername], dbName | |
REVERT | |
END TRY | |
BEGIN CATCH | |
SELECT | |
@errorNumber = ERROR_NUMBER(), | |
@errorSeverity = ERROR_SEVERITY(), | |
@errorState = ISNULL(NULLIF(ERROR_STATE(), 0), 1), --don't allow state 0 to be passed in to raiserror | |
@errorLine = ERROR_LINE(), | |
@errorMessage = N'Error %d, Level %d, State %d. Error message: ' + ERROR_MESSAGE(); | |
RAISEERROR(@errorMessage, @errorSeverity, 1, @errorNumber, @errorSeverity, @errorState, @errorLine); | |
END CATCH; | |
-- Exit Block | |
SET NOCOUNT OFF; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment