Listing all stored procedures with their security config
For a code review at work, I had to write a code review document. among other things, I had to list the different stored procedures in my database, together with the security configuration of the stored procedures. A bit of googling got me the various pieces of the puzzle, and I put them together for my purpose:
http://blog.sqlauthority.com/2007/11/27/sql-server-2005-list-all-stored-procedure-in-database/
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/sql-server-2005_3a00_-view-all-permissions.aspx
select o.NAME AS object_name,
dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.all_objects o
left OUTER JOIN sys.database_permissions p
on p.major_id = o.OBJECT_ID
left outer JOIN sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where o.type= 'p' and is_ms_shipped=0 and o.[name] not like 'sp[_]%diagram%'
order by object_name
I used an outer join for the database permissions instead of an inner join like the person whose example I copied, because I wanted to show all procedures, even if they did not have security configuration attached.