Query SQL Server 2005 Permissions

The following query joins some of the system views together to show a report of the permissions assigned in the current database. Set the @Principal ID value to the name of a specific user or role, or leave it as NULL to view all.

DECLARE @Principal sysname
SET @Principal = NULL — set this to a specific user or role name if desired.

prin.name AS PrincipalName,
prin.type_desc AS PrincipalType,
WHEN perm.class=1 and perm.minor_id = 0 THEN ‘OBJECT’
WHEN perm.class=1 and perm.minor_id = 0 THEN ‘COLUMN’
ELSE perm.class_desc
END AS SecurableType,
sch.name AS SchemaName,
obj.name AS ObjectName,
IsNull(col.name,”) AS ColumnName,
state_desc AS PermissionState,
permission_name AS Permission
FROM sys.database_principals AS prin
JOIN sys.database_permissions AS perm
ON prin.principal_ID = perm.grantee_principal_ID
JOIN sys.objects AS obj
ON perm.major_id = obj.object_id
AND perm.minor_id = 0
LEFT JOIN sys.columns AS col
ON perm.major_id = col.object_id
AND col.column_id = perm.minor_id
JOIN sys.schemas AS sch
ON obj.schema_id = sch.schema_id
WHERE @Principal IS NULL OR prin.name=@Principal

One Response

Leave a Reply