I am Berke

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:

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