Post reply

Find all server level permissions assigned to user defined SERVER roles

  • February 13, 2023 at 3:16 pm

    #4149063

    Please help me with a T-SQL for finding all server level permissions assigned to user defined SERVER roles such as VIEW ANY DATABASE, VIEW SERVER STATE, SHUTDOWN, ETC.

    For example, in the screenshot attached, for the test role, it has VIEW ANY DATABASE.

    what's the T-SQL to find the permissions for test server role.

    Thanks

    Attachments:
    You must be logged in to view attached files.

  • February 13, 2023 at 3:48 pm

    #4149078 

    SELECT prin.name, perm.permission_name
    FROM sys.server_principals prin
    INNER JOIN sys.server_permissions perm
    ON prin.principal_id = perm.grantee_principal_id
    WHERE prin.type = 'R' and prin.name = 'test'

    Replace 'test' with the name of the role you have and this should do what you need

  • February 13, 2023 at 4:17 pm

    #4149084

    Ant-Green wrote:

    SELECT prin.name, perm.permission_name
    FROM sys.server_principals prin
    INNER JOIN sys.server_permissions perm
    ON prin.principal_id = perm.grantee_principal_id
    WHERE prin.type = 'R' and prin.name = 'test'

    Replace 'test' with the name of the role you have and this should do what you need

    very helpful!  Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply