asked
Script out all stored procedures with permissions?
Hi all, I want to do an audit of all our stored procs on one of our dbs and what the permissions are to execute... i can't seem to find anything on the net... how do i do this? i've tried looking at sys.procedures, sys.database_principals and sys.syspermissions but there doesn't seem to be a key i can join on... any ideas? Thanks!
Permissions are stored in [`sys.database_permissions`][1]. Not only can you have execute permission directly on the stored proc, you can also have execute permission on a schema, thereby giving you permissions on all contained objects, and also execute permission at the database level. [1]:
http://msdn.microsoft.com/en-us/library/ms188367.aspx
Yes, I totally agree with Kev Riley. But if you do want to know the joining key then it is major_id. For e.g. this script would give you the idea SELECT USER_NAME(grantee_principal_id) AS 'User' , state_desc AS 'Permission' , permission_name AS 'Action' , CASE class WHEN 0 THEN 'Database::' + DB_NAME() WHEN 1 THEN OBJECT_NAME(major_id) WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id) END AS 'Securable' FROM sys.database_permissions dp INNER JOIN sys.[procedures] AS P ON [dp].[major_id] = [P].[object_id] WHERE class IN (0, 1, 3) AND minor_id = 0; If you want to see more details then either remove the join with "sys.procedures" OR convert the INNER JOIN into LEFT JOIN.
Well if you would have followed Kev Riley's provided link you would have got the information ;) But this helps to filter the classes on which permission exists. Generally, 0 = Database, 1 = Object or Column, 3 = Schema are the classes we are interested in. Hence, I segregated these classes to make a more sensible output by using CASE expression. Hope it clarifies the usage.
Obviously to Kev Riley. The key information as well as the advice given worth more than an accepted answer.
9 People are following this question.
