1

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://learn.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.

enter image description here

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.

2
  • I'm not sure I understand your question. the picture you provided already shows the privileges of each server role (and they come with the listed privileges by default, you don't need to give them those permissions, in fact, the link you provided states that "The permissions that are granted to the fixed server roles (except public) cannot be changed.").
    – Ronaldo
    Commented Nov 18, 2020 at 17:41
  • @Ronaldo you are right. But my goal is to have a program (in C#, but it doesn't matter) which can fetch system roles and their permissions from different MS SQL Server installations. I'm sure they will be of different versions. So I'm not sure if this picture is true for ALL possible versions. This is why it would be better to query server for this info. Now it looks like I can get system roles but not their permissions..
    – berliner
    Commented Nov 18, 2020 at 18:54

1 Answer 1

0

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
1
  • Not exactly, as it doesn't return fixed server roles
    – berliner
    Commented Nov 19, 2020 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.