SQL Server 2005: View all permissions

SQL Server 2005: View all permissions

Script: 1

WITH    perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) 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,

sp.name as login

       from    sys.database_permissions p

       left JOIN sys.database_principals dp on     p.grantee_principal_id = dp.principal_id

left Join sys.server_principals sp on dp.sid = sp.sid

)

-- users

SELECT p.principal_name,  p.principal_type_desc, login, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast('<granted explicit>' as sysname) as role_name

FROM    perms_cte p

UNION

-- role members

SELECT rm.member_principal_name, rm.principal_type_desc, rm.login, p.class_desc, p.[object_name], p.permission_name, coalesce(p.permission_state_desc, '<Member of fixed database-role:>'), 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, sp.name as login

   from    sys.database_role_members rm

   INNER   JOIN sys.database_principals dp ON     rm.member_principal_id = dp.principal_id

left Join sys.server_principals sp on dp.sid = sp.sid

) rm

ON     rm.role_principal_id = p.principal_id

order by 2, 1, 4, 5, 6, 8

Script: 2

select  su.name AS principal_name,

       dp.type_desc AS principal_type_desc,

       ao.type_desc,

       ao.name AS object_name,

       p.permission_name,

       p.state_desc AS permission_state_desc

from    master.sys.database_permissions p,

       master.sys.database_principals dp,

       master.sys.all_objects ao,

       master.sys.sysusers su

where ao.OBJECT_ID = p.MAJOR_ID

 and p.grantee_principal_id = dp.principal_id

 and p.GRANTEE_PRINCIPAL_ID = su.UID

 --and dp.name = 'guest'