Skip to content

Instantly share code, notes, and snippets.

@dhmacher
Created September 13, 2023 12:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dhmacher/efc26c2adaaba1f37478275b72f32159 to your computer and use it in GitHub Desktop.
Save dhmacher/efc26c2adaaba1f37478275b72f32159 to your computer and use it in GitHub Desktop.
Script out server-level principals and their memberships in fixed server roles.
DECLARE @principals TABLE (
seq int IDENTITY(1, 1) NOT NULL,
principal_id int NOT NULL,
[sql] nvarchar(max) NOT NULL,
PRIMARY KEY CLUSTERED (seq)
);
--- Windows logins and groups:
INSERT INTO @principals (principal_id, [sql])
SELECT sp.principal_id,
N'IF (SUSER_SID(N'+QUOTENAME(sp.[name], N'''')+N') IS NULL) '+
N'CREATE LOGIN '+QUOTENAME(sp.[name])+N' '+
N'FROM WINDOWS '+
N'WITH '+
N'DEFAULT_DATABASE='+QUOTENAME(ISNULL(sp.default_database_name, N'master'))+N', '+
N'DEFAULT_LANGUAGE='+QUOTENAME(ISNULL(sp.default_language_name, N'us_english'))+N';'
FROM master.sys.server_principals AS sp
WHERE sp.[type] IN ('U', 'G');
--- SQL Server logins:
INSERT INTO @principals (principal_id, [sql])
SELECT sp.principal_id,
N'IF (SUSER_SID(N'+QUOTENAME(sp.[name], N'''')+N') IS NULL)'+
N'CREATE LOGIN '+QUOTENAME(sp.[name])+N' '+
N'WITH '+
N'PASSWORD=0x'+CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+
N'CHECK_POLICY=OFF, '+
N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N', '+
N'DEFAULT_DATABASE='+QUOTENAME(ISNULL(sp.default_database_name, N'master'))+N', '+
N'DEFAULT_LANGUAGE='+QUOTENAME(ISNULL(sp.default_language_name, N'us_english'))+N';'
FROM master.sys.server_principals AS sp
INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
WHERE sp.[type]='S' AND sp.[sid]!=0x01;
--- Disabled logins:
INSERT INTO @principals (principal_id, [sql])
SELECT sp.principal_id,
N'ALTER LOGIN '+QUOTENAME(sp.[name])+N' DISABLE;'
FROM master.sys.server_principals AS sp
WHERE sp.principal_id IN (SELECT principal_id FROM @principals) AND sp.is_disabled=1;
--- Permissions on SERVER (not endpoints or other server-level objects)
INSERT INTO @principals (principal_id, [sql])
SELECT sp.principal_id,
(CASE WHEN p.[state]='D' THEN N'DENY'
WHEN p.[state] IN ('G', 'W') THEN N'GRANT' END)+N' '+p.[permission_name]+N' TO '+QUOTENAME(sp.[name] COLLATE database_default)+
(CASE WHEN p.[state]='W' THEN N' WITH GRANT' ELSE N'' END)+N';'
FROM sys.server_permissions AS p
INNER JOIN sys.server_principals AS sp ON
p.grantee_principal_id=sp.principal_id
WHERE p.[class]=100
AND p.[state_desc] IN ('GRANT', 'DENY', 'GRANT_WITH_GRANT_OPTION')
AND p.grantee_principal_id IN (SELECT principal_id FROM @principals);
--- Fixed server role memberships:
INSERT INTO @principals (principal_id, [sql])
SELECT m.principal_id,
N'ALTER SERVER ROLE '+QUOTENAME(r.[name])+N' ADD MEMBER '+QUOTENAME(m.[name])+N';'
FROM sys.server_principals AS r
INNER JOIN sys.server_role_members AS srm ON r.principal_id=srm.role_principal_id
INNER JOIN sys.server_principals AS m ON srm.member_principal_id=m.principal_id
WHERE m.principal_id IN (SELECT principal_id FROM @principals)
AND r.is_fixed_role=1;
--- Display the results:
SELECT [sql]
FROM @principals
ORDER BY seq;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment