SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)
server_principals: Contains a row for every server-level principal.
SELECT sp.[Name] AS ServerPrincipal, sp.[type_desc] AS LoginType, CASE sp.is_disabled WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' END AS UserDisabled, sp.create_date AS DateCreated, sp.modify_date AS DateModified, sp.default_database_name AS DefaultDB, sp.default_language_name AS DefaultLang, ISNULL(STUFF(( SELECT ',' + CASE ssp22.[Name] WHEN 'sysadmin' THEN ssp22.[Name] + ' "Full privilages"' ELSE ssp22.[Name] END FROM [sys].server_principals ssp2 INNER JOIN [sys].server_role_members ssrm2 ON ssp2.principal_id = ssrm2.member_principal_id INNER JOIN [sys].server_principals ssp22 ON ssrm2.role_principal_id = ssp22.principal_id WHERE ssp2.principal_id = sp.principal_id ORDER BY ssp2.[Name] FOR XML PATH (N''), TYPE ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No Roles Held') AS ListofServerRoles, ISNULL(STUFF(( SELECT ';' + ' Permission [' + sspm3.[permission_name] + '] is [' + CASE WHEN sspm3.[state_desc] = 'GRANT' THEN 'Granted]' WHEN sspm3.[state_desc] = 'DENY' THEN 'Denied]' END AS PermGrants FROM [sys].server_principals ssp3 INNER JOIN [sys].server_permissions sspm3 ON ssp3.principal_id = sspm3.[grantee_principal_id] WHERE sspm3.[class] = 100 AND sspm3.[grantee_principal_id] = sp.principal_id FOR XML PATH (N''), TYPE ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''), 'No Server Permissions') + ' in Server::' + @@ServerName + '' AS PermGrants FROM [sys].server_principals sp WHERE sp.[Type] IN ('S', 'G', 'U') AND sp.[Name] NOT LIKE '##%##' ORDER BY ServerPrincipal;
Other Useful Query
Returns a description of the built in permissions hierarchy of the server
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name; SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
login and server role mapping
--->> login and server role mapping SELECT spU.name ,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin ,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin ,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin ,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin ,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin ,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin ,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator ,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin FROM sys.server_principals AS spR JOIN sys.server_role_members AS srm ON spR.principal_id = srm.role_principal_id JOIN sys.server_principals AS spU ON srm.member_principal_id = spU.principal_id WHERE spR.[type] = 'R' GROUP BY spU.name
Orphaned Database User discovery login user maping
exec sp_change_users_login @Action=’Report’ ;