Today I was asked how one can query the server to list out all permissions that a principal has on all the securables in a SQL Server database. After 10 mins of searching through BOL I came up with the following that seems to work quite well:
1 select dp.NAME AS principal_name,
2 dp.type_desc AS principal_type_desc,
3 o.NAME AS object_name,
4 p.permission_name,
5 p.state_desc AS permission_state_desc
6 from sys.database_permissions p
7 left OUTER JOIN sys.all_objects o
8 on p.major_id = o.OBJECT_ID
9 inner JOIN sys.database_principals dp
10 on p.grantee_principal_id = dp.principal_id
Here's the results:
I'm only putting it here cos I figure this'll be useful one day! Whether its useful to me or someone else is a different matter!! Note that this only works on SQL Server 2005, not on earlier versions.
-Jamie
UPDATE: I've posted a newer, better, version of this here: http://blogs.conchango.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx