3

I can separately query all the server or database level permissions, but I want to know other thing. What server role do the given permissions exactly belong to? For example I want to see all the permissions belonging to securityadmin server role, hence I'll be able to understand what privileges the role has. I want the same for database roles. Can I find this info in SQL Server system tables?

Thanks

igelr
  • 2,162
  • 3
  • 26
  • 56

2 Answers2

1

Hope the following would help:

Server Level permissions

  1. For server role permissions sp_srvrolepermission

  2. For Logins permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        P.state_desc 
from sys.server_permissions as p
        left join sys.server_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.server_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id

Database Level Permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        state_desc
from sys.database_permissions as p
        left join sys.database_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.database_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id
go
Shekar Kola
  • 2,477
  • 2
  • 10
  • 24
0

If this can help you

--List all permissions for a given role

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]
WHERE p.[name] = '<role_name>'
Kris
  • 452
  • 5
  • 15