MS SQL: get permissions of a fixed server role

Question

I need to get a list of MS SQL Server fixed roles and their permissions.

There is a query to get all server-level permissions:

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

And there is a SP sys.sp_srvrolepermission which returns list of server roles and their 'permissions'. But those permissions don't really map to what the previous query returns.

There is a pretty good explanation here https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 with image, which shows roles and permissions. And permissions on the image map to the first query results.

The question is - is there a correct way (SP, query) to get server fixed roles to permissions mapping, or should I hardcode those as per image.

Answer 1

Maybe this is what you are looking for:

SELECT SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID, 
       ROLE.NAME AS ROLENAME, 
       SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID, 
       MEMBER.NAME AS MEMBERNAME, 
       MEMBER.TYPE_DESC, 
       PERMISSION_NAME, 
       STATE_DESC, 
       CLASS_DESC
FROM SYS.SERVER_ROLE_MEMBERS
     FULL JOIN SYS.SERVER_PRINCIPALS AS ROLE ON SYS.SERVER_ROLE_MEMBERS.ROLE_PRINCIPAL_ID = ROLE.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PRINCIPALS AS MEMBER ON SYS.SERVER_ROLE_MEMBERS.MEMBER_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID
     FULL JOIN SYS.SERVER_PERMISSIONS PERMISSIONS ON PERMISSIONS.GRANTEE_PRINCIPAL_ID = MEMBER.PRINCIPAL_ID