Two days ago I posted a blog entry here that showed how the following SQL statement:
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o
on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
could be used to produce a list of permissions defined in a SQL Server database. It worked OK but I took a look at it and decided it could be better so today I wrote the following, more concise version (and with an exta column - class_desc):
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
Again though, I thought I could make this better. The above statements will give me all the defined permissions. However this only gives you the roles on which permissions are defined. It doesn't allow you to explode the roles to the role members and display their inherited permissions. That is what the following script does:
WITH perms_cte as
(
select USER_NAME(p.grantee_principal_id) AS principal_name,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
inner JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM perms_cte p
WHERE principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM perms_cte p
right outer JOIN (
select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
order by 1
Here's the output it produces. Note the last column which defines whether the permission is defined explicitly or whether it is inherited from a role:
As before, this will only work on SQL Server 2005 and beyond.
There may of course be a hundred and one other ways of achieving this, and I expect many of you already have your own method, but this works for me. For now.
-Jamie
UPDATE, 2008-02-26: This query does not return information about the 'public' database role. This is because sys.database_role_members does not contain info about that role. This kind of makes sense given that the public role "Cannot have users, groups, or roles assigned to it because they belong to the role by default"