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.

Leave a Reply

Your email address will not be published. Required fields are marked *