T-SQL:
Declare @RoleName as SysName
Set @RoleName = ''
SELECT
[Name] = pri.name,
[SecurityPrincipalType] = pri.type_desc,
[PermissionType] = per.class_desc,
[ObjectType] = CASE WHEN obj.type_desc IS NULL OR obj.type_desc = 'SYSTEM_TABLE' THEN per.class_desc ELSE obj.type_desc END,
[ObjectName] = Isnull(sch.name, Object_name(per.major_id)),
[Permission Name] = per.permission_name,
[StateDesc] = per.state_desc,
[SchemaName] = sch2.Name
FROM
sys.database_principals pri
INNER JOIN sys.database_permissions per ON per.grantee_principal_id = pri.principal_id
LEFT JOIN sys.schemas sch ON per.major_id = sch.schema_id
LEFT JOIN sys.objects obj ON per.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas sch2 ON sch2.schema_id = obj.schema_id
WHERE
pri.type_desc = 'DATABASE_ROLE'
AND per.class_desc <> 'DATABASE'
AND pri.name not in ('public')
AND (@RoleName = '' OR pri.name = @RoleName)
AND sch2.Name IS NOT NULL
ORDER BY
pri.name asc,
pri.type_desc asc,
ObjectType desc,
ObjectName asc
|
|
|
|
|
|