How do I identify all super users in SQL Server?

For a security audit,I need to identify all super users and log every activity performed. Best approach for this?

By "super user" I mean sysadmin, serveradmin, securityadmin, processadmin, and dbcreator.

You can use this code:

SELECT   name,type_desc,is_disabled, 
         (IS_SRVROLEMEMBER ('sysadmin',name)) AS ISSRVROLE
FROM     master.sys.server_principals 
-- WHERE    IS_SRVROLEMEMBER ('sysadmin',name) = 1
ORDER BY name ;

sys.server_principals - Contains a row for every server-level principal

IS_SRVROLEMEMBER - Indicates whether a SQL Server login is a member of the specified server role.

You would also need to check for the sysadmin-equivalent CONTROL SERVER permission for the server principal in sys.server_permissions.

IS_SRVROLEMEMBER can return:

• 0 - login is not a member of role.

• 1 - login is a member of role.

• NULL - role or login is not valid, or you do not have permission to view the role membership.

StackExchange Database Administrators Q#193819, answer score: 8