0

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.

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.

  • FYI, sys.sp_srvrolepermission is deprecated and marked as to be removed from SQL Server, per the documentation. This, however, looks like a question for Database Administrators, not Stack Overflow. – Larnu Nov 18 at 11:10
  • 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 Nov 18 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 Nov 18 at 18:54
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
| improve this answer | |
  • Not exactly, as it doesn't return fixed server roles – berliner Nov 19 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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