Skip to content

Instantly share code, notes, and snippets.

@petesql
Last active January 22, 2024 21:36
Show Gist options
  • Save petesql/1ff590618456b21b9f814c00aac05120 to your computer and use it in GitHub Desktop.
Save petesql/1ff590618456b21b9f814c00aac05120 to your computer and use it in GitHub Desktop.
SQL Server User & Permissions Audit Script
-- 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