SQL Server Security Audit

SQL Server Security Audit

---

To find out what login accounts are members of this role [Working]

SELECT c.name AS Sysadmin_Server_Role_Members
FROM sys.server_principals a
INNER JOIN sys.server_role_members b
ON a.principal_id = b.role_principal_id AND a.type = 'R'
AND a.name ='sysadmin'
INNER JOIN sys.server_principals c
ON b.member_principal_id = c.principal_id

Result

To get a list of server level permissions for logins

SELECT a.name AS Login_Name,
a.type_desc AS LoginType,
b.permission_name AS Permission
FROM sys.server_principals a
INNER JOIN sys.server_permissions b
ON a.principal_id = b.grantee_principal_id
WHERE a.type <> 'R' -- Excludes Server Role
AND a.name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND a.name <> 'sa' -- Excludes the obvious system administrator
AND b.state = 'G' -- Granted
ORDER BY a.name

Result

To find out which logins have master as the default database

USE master
GO
SELECT name as LoginName, type_desc AS LoginType
FROM sys.server_principals
WHERE type <> 'R' -- Excludes Server Role
AND name NOT LIKE '##MS_%' -- Excludes system level certificate mapped logins
AND name <> 'sa' -- Excludes the obvious system administrator
AND default_database_name = 'master'