Início / dba / Perguntas / 155015
Accepted
Matthew Verstraete
Asked: 2016-11-12 13:09:43 +0800 CST

Obter lista de permissões para uma função SQL

  • 772

Eu tenho uma função nomeada db_executore preciso descobrir quais permissões ela possui em quais objetos. Encontrei Listar todas as permissões para uma determinada função? mas a única solução que funciona remotamente é:

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] = 'db_executor'

O problema é que o ObjectNamee ObjectTypeé NULL. Portanto, embora eu saiba que ele só tem EXECUTEpermissão em um estado de GRANTnão sei a que objeto (s) isso se aplica. Existe uma maneira melhor de obter essa lista ou como posso modificar esse código para listar o (s) objeto (s)?

sql-server sql-server-2012

2 respostas

  1. Best Answer

    O script não possui uma junção à esquerda sys.typespara obter informações do tipo de tabela, que também requer permissões de execução para uso (alternativamente, join sys.table_types, você obterá os mesmos dados). Tente o seguinte:

    SELECT  DB_NAME() AS 'DBName' ,
    p.[name] AS 'PrincipalName' ,
    p.[type_desc] AS 'PrincipalType' ,
    p2.[name] AS 'GrantedBy' ,
    dbp.[permission_name] ,
    dbp.[state_desc] ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[name], t.name)
    END AS 'ObjectName' ,
    CASE WHEN [dbp].[class_desc] = 'DATABASE' THEN 'DATABASE'
         ELSE COALESCE(so.[type_desc], N'TYPE')
    END AS 'ObjectType' 
FROM    [sys].[database_permissions] dbp
        LEFT JOIN [sys].[all_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]
        LEFT JOIN [sys].[types] t ON dbp.major_id = t.user_type_id
WHERE   p.[name] = 'db_executor';
    • 1

  2. O código abaixo fornece todas as funções

    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'
        --, so.*
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]

    Encontrei apenas ObjectName, ObjectType apenas para função pública

    insira a descrição da imagem aqui

    E os seguintes valores

    insira a descrição da imagem aqui

    • 1

relate perguntas