Building upon my post from last Tuesday, if you know all the roles for a given user, you'll probably want all the permissions as well. In prior versions of SQL Server, the way to go was the system stored procedure sp_helprotect. However, sp_helprotect is stuck in legacy SQL Server 2000 permissions. In other words, the new securables in SQL Server 2005, such as SCHEMA, aren't reported by this stored procedure. They key is to use the dynamic management view (DMV), sys.database_permissions.
The SQL Doctor, Louis Davidson, posted on how to use this DMV to query table permissions about a year ago. Taking that and combining it with the CTE for listing all the roles for a user, we come up with:
DECLARE @username NVARCHAR(128);
SET @username = '<Database User>';
WITH CTE_Roles (role_principal_id)
AS
(
SELECT role_principal_id
FROM sys.database_role_members
WHERE member_principal_id = USER_ID(@username)
UNION ALL
SELECT drm.role_principal_id
FROM sys.database_role_members drm
INNER JOIN CTE_Roles CR
ON drm.member_principal_id = CR.role_principal_id
)
SELECT DISTINCT
USER_NAME(CR.role_principal_id) PrincipalName,
COALESCE(SO.type_desc, DPerms.class_desc) ObjectType,
CASE DPerms.class
WHEN 1 THEN
CASE DPerms.minor_id
WHEN 0 THEN SCHEMA_NAME(SO.schema_id) + '.' + SO.name
ELSE SCHEMA_NAME(SO.schema_id) + '.' + SO.name + ' ('
+ COL_NAME(DPerms.major_id, DPerms.minor_id) + ')' END
WHEN 3 THEN SCHEMA_NAME(DPerms.major_id) END ObjectName,
DPerms.state_desc + ' ' + DPerms.permission_name Permission
FROM (
SELECT role_principal_id
FROM CTE_Roles
UNION ALL
SELECT USER_ID('public')
UNION ALL
SELECT USER_ID(@username)) CR
INNER JOIN sys.database_permissions DPerms
ON CR.role_principal_id = DPerms.grantee_principal_id
LEFT JOIN sys.objects SO
ON DPerms.major_id = SO.OBJECT_ID
WHERE DPerms.class IN (1, 3)
AND DPerms.major_id > 0
ORDER BY PrincipalName, ObjectName, Permission;
Technorati Tags:
DATABASE |
SQL |
T-SQL |
Security |
