Adatbázis szintű jogok kilistázása

 1USE [?]
 2GO
 3  
 4SELECT 
 5    DB_ID() AS [db_id],
 6    DB_NAME() AS [db_name],
 7    DPER.[class_desc],
 8    DPER.[permission_name],
 9    DPER.[state_desc],
10    DPRI.[name],
11    DPRI.[type_desc],
12    CASE 
13        WHEN DPER.[class_desc] = 'DATABASE' THEN DB_NAME()
14        WHEN DPER.[class_desc] = 'OBJECT_OR_COLUMN' AND DPER.[minor_id] = 0 THEN SCHEMA_NAME(O.[schema_id]) + '.' +OBJECT_NAME(DPER.[major_id])
15        WHEN DPER.[class_desc] = 'OBJECT_OR_COLUMN' AND DPER.[minor_id] != 0 THEN SCHEMA_NAME(O.[schema_id]) + '.' + OBJECT_NAME(DPER.[major_id]) + '(' + C.[name] + ')'
16        WHEN DPER.[class_desc] = 'SCHEMA' THEN SCHEMA_NAME(DPER.[major_id])
17        WHEN DPER.[class_desc] = 'DATABASE_PRINCIPAL' THEN (SELECT [name] FROM sys.database_principals WHERE [principal_id] = DPER.[major_id])
18        WHEN DPER.[class_desc] = 'ASSEMBLY' THEN (SELECT [clr_name] COLLATE DATABASE_DEFAULT FROM sys.assemblies WHERE [assembly_id] = DPER.[major_id])
19        WHEN DPER.[class_desc] = 'TYPE' THEN TYPE_NAME(DPER.[major_id])
20        WHEN DPER.[class_desc] = 'XML_SCHEMA_COLLECTION' THEN (SELECT [name] FROM sys.xml_schema_collections WHERE [xml_collection_id] = DPER.[major_id])
21        WHEN DPER.[class_desc] = 'MESSAGE_TYPE' THEN (SELECT [name] FROM sys.service_message_types WHERE [message_type_id] = DPER.[major_id])
22        WHEN DPER.[class_desc] = 'SERVICE_CONTRACT' THEN (SELECT [name] FROM sys.service_contracts WHERE [service_contract_id] = DPER.[major_id])
23        WHEN DPER.[class_desc] = 'SERVICE' THEN (SELECT [name] FROM sys.services WHERE [service_id] = DPER.[major_id])
24        WHEN DPER.[class_desc] = 'REMOTE_SERVICE_BINDING' THEN (SELECT [name] FROM sys.remote_service_bindings WHERE [remote_service_binding_id] = DPER.[major_id])
25        WHEN DPER.[class_desc] = 'ROUTE' THEN (SELECT [name] FROM sys.routes WHERE [route_id] = DPER.[major_id])
26        WHEN DPER.[class_desc] = 'FULLTEXT_CATALOG' THEN (SELECT [name] FROM sys.fulltext_catalogs WHERE [fulltext_catalog_id] = DPER.[major_id])
27        WHEN DPER.[class_desc] = 'SYMMETRIC_KEY' THEN (SELECT [name] FROM sys.symmetric_keys WHERE [symmetric_key_id] = DPER.[major_id])
28        WHEN DPER.[class_desc] = 'CERTIFICATE' THEN (SELECT [name] FROM sys.certificates WHERE [certificate_id] = DPER.[major_id])
29        WHEN DPER.[class_desc] = 'ASYMMETRIC_KEY' THEN (SELECT [name] FROM sys.asymmetric_keys WHERE [asymmetric_key_id] = DPER.[major_id])
30        ELSE OBJECT_NAME(DPER.[major_id])
31        END    AS [object_name],
32        GR.[name] AS [grantor_principal]
33FROM 
34    sys.database_permissions DPER
35JOIN
36    sys.database_principals DPRI ON  DPER.[grantee_principal_id] = DPRI.[principal_id]
37JOIN
38    sys.database_principals GR ON DPER.[grantor_principal_id] = GR.[principal_id]
39LEFT OUTER JOIN
40    sys.objects O ON DPER.[major_id] = O.[object_id]
41LEFT OUTER JOIN
42    sys.columns C ON DPER.[major_id] = C.[object_id] AND DPER.[minor_id] = C.[column_id]
43WHERE
44    [DPER].[major_id] > -1 --excludes system objects