Adatbázis szintű jogok kilistázása

Az utóbbbi időben sokszor kérdezték különböző fórumokon, hogyan lehet kilistázni az adatbázis szintű jogokat. az alábbi script ezt megcsinálja:

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

 

A kód csak SQL Server 2008 R2-vel lett tesztelve. Egyébként itt is elérhető: http://code.msdn.microsoft.com/Get-all-database-d2d7946e

Add comment