Audit of Logins
Recently I had to review all logins on a server and get telemetry data related to them. The below is what I settled on. In the where clause at the end, you can choose what kind of accounts you wish to look at. I knew I wanted to omit the system accounts beginning with ## and at the time I only looked for SQL Auth accounts where are type S. I included U and G for Windows User and Windows Groups. You could also include C or K for certificates and keys, or you could include E and X for AzureAD groups and users. I ran this against a Registered Server Group to tackle all the servers at once and give the client a nice output.
-- Create temp table for database permissions
CREATE TABLE #DatabasePermissions (
LoginName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS,
DatabaseName NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CI_AS,
DatabaseRoles NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS,
ExplicitPermissions NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS
);
-- Build and execute dynamic SQL to populate database permissions
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql +
'INSERT INTO #DatabasePermissions (LoginName, DatabaseName, DatabaseRoles, ExplicitPermissions)
SELECT dp.name COLLATE SQL_Latin1_General_CP1_CI_AS as LoginName,
''' + name + ''' as DatabaseName,
STUFF((
SELECT '', '' + r.name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM [' + name + '].sys.database_role_members rm
INNER JOIN [' + name + '].sys.database_principals r ON rm.role_principal_id = r.principal_id
WHERE rm.member_principal_id = dp.principal_id
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') AS DatabaseRoles,
STUFF((
SELECT '', '' + p.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + '' ('' + p.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + '')''
FROM [' + name + '].sys.database_permissions p
WHERE p.grantee_principal_id = dp.principal_id
AND p.major_id = 0
FOR XML PATH(''''), TYPE
).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') AS ExplicitPermissions
FROM [' + name + '].sys.database_principals dp
WHERE dp.type = ''S''
AND dp.name COLLATE SQL_Latin1_General_CP1_CI_AS IN (
SELECT name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.server_principals
WHERE type = ''S''
AND name NOT LIKE ''##%''
);
'
FROM sys.databases d
WHERE d.state = 0 -- Only online databases
AND d.name NOT IN ('tempdb')
-- Only include databases that are primary on this node
AND (
-- For Always On AG databases: only if primary replica
NOT EXISTS (
SELECT 1 FROM sys.availability_databases_cluster adc
JOIN sys.dm_hadr_availability_replica_states rs ON adc.group_id = rs.group_id
WHERE adc.database_name = d.name
AND rs.is_local = 1
AND rs.role_desc != 'PRIMARY'
)
-- For mirrored databases: only if principal (mirroring_role = 1) or not mirrored
AND NOT EXISTS (
SELECT 1 FROM sys.database_mirroring dm
WHERE dm.database_id = d.database_id
AND dm.mirroring_role = 2 -- 2 = Mirror (secondary)
)
-- For log shipping: exclude if it's a secondary (read-only with restricted_user access)
AND NOT (d.is_read_only = 1 AND d.user_access_desc = 'RESTRICTED_USER')
);
-- Execute the dynamic SQL to populate temp table
EXEC sp_executesql @sql;
-- Main query with database permissions joined
SELECT
-- Login Information
sp.name COLLATE SQL_Latin1_General_CP1_CI_AS AS LoginName,
sp.type_desc AS LoginType,
sp.is_disabled AS IsDisabled,
sp.create_date AS CreateDate,
sp.modify_date AS ModifyDate,
sp.default_database_name AS DefaultDatabase,
sp.default_language_name AS DefaultLanguage,
-- SQL Login specific information (from sys.sql_logins)
sl.is_policy_checked AS PasswordPolicyEnabled,
sl.is_expiration_checked AS PasswordExpirationEnabled,
CASE
WHEN LOGINPROPERTY(sp.name, 'IsLocked') IS NULL THEN 'Not Applicable'
WHEN LOGINPROPERTY(sp.name, 'IsLocked') = 1 THEN 'Locked'
ELSE 'Unlocked'
END AS LockStatus,
-- Password Information (for SQL logins)
CASE
WHEN sl.password_hash IS NULL THEN 'N/A'
WHEN PWDCOMPARE('', sl.password_hash) = 1 THEN 'Empty Password'
WHEN PWDCOMPARE(sp.name COLLATE SQL_Latin1_General_CP1_CI_AS, sl.password_hash) = 1 THEN 'Password Same as Login'
ELSE 'Password Set'
END AS PasswordStatus,
-- Server Role Memberships (compatible with SQL Server 2012+)
STUFF((
SELECT ', ' + sr2.name COLLATE SQL_Latin1_General_CP1_CI_AS
FROM sys.server_role_members srm2
INNER JOIN sys.server_principals sr2 ON srm2.role_principal_id = sr2.principal_id
WHERE srm2.member_principal_id = sp.principal_id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ServerRoles,
-- Database Level Permissions (formatted as: DB1: role1, role2; DB2: role3)
STUFF((
SELECT '; ' +
CASE
WHEN dp.DatabaseRoles IS NOT NULL AND dp.ExplicitPermissions IS NOT NULL
THEN dp.DatabaseName + ': ' + dp.DatabaseRoles + ' | ' + dp.ExplicitPermissions
WHEN dp.DatabaseRoles IS NOT NULL
THEN dp.DatabaseName + ': ' + dp.DatabaseRoles
WHEN dp.ExplicitPermissions IS NOT NULL
THEN dp.DatabaseName + ': ' + dp.ExplicitPermissions
ELSE dp.DatabaseName + ': (access only)'
END
FROM #DatabasePermissions dp
WHERE dp.LoginName COLLATE SQL_Latin1_General_CP1_CI_AS = sp.name COLLATE SQL_Latin1_General_CP1_CI_AS
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS DatabasePermissions
FROM sys.server_principals sp
-- Join with sys.sql_logins for SQL authentication specific columns
LEFT JOIN sys.sql_logins sl ON sp.principal_id = sl.principal_id
WHERE
-- Filter for SQL Server Authentication logins only
sp.type IN ('S','U','G')
AND sp.name NOT LIKE '##%' -- Exclude system logins
ORDER BY sp.name COLLATE SQL_Latin1_General_CP1_CI_AS;
-- Clean up temp table
DROP TABLE #DatabasePermissions;
dbWonderKid 