how to find the list of stored procedures with public execute permission

Question

I have a db with more than 1000s of stored procedures. Need a query to find the list of stored procedures which has public execute permission.

Query I tried which didn't give me clear output.

select name, 
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute,
    has_perms_by_name(name, 'OBJECT', 'VIEW DEFINITION') as has_view_definition
from sys.procedures

Maybe this will help you https://stackoverflow.com/questions/13152329/finding-stored-procedures-having-execute-permission — Valerica, Feb 20 '18 at 09:31
Which dbms are you using? Have you checked information_schema? — jarlh, Feb 20 '18 at 09:31

score 0 · Answer 1 · answered Feb 20 '18 at 10:40

Not sure why you got voted down, seems like a reasonable question to me.

You'll need something along the lines of:

DECLARE @principal_id int

SELECT @principal_id = principal_id
  FROM sys.database_principals
 WHERE name = 'public'

SELECT o.name, 
       dp.permission_name,
       dp.state_desc
  FROM sys.objects o 
  JOIN sys.database_permissions  dp
    ON dp.class = 1
   AND dp.major_id = o.object_id
   AND dp.grantee_principal_id = @principal_id
   AND dp.type = 'EX' -- execute
   AND dp.state IN ('G', 'W')
 WHERE o.type = 'P' -- stored procedures

how to find the list of stored procedures with public execute permission

1 Answers1