Finding stored procedures having execute permission

sql-server, sql-server-2008-r2, stored-procedures

Solution

Use `HAS_PERMS_BY_NAME`:

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

Problem

I am using SQL Server 2008 R2. I need to list out all the stored procedures that a database user (MYUSER) has execute permission. Also, I need to list out which are the stored procedures that the user does NOT have EXECUTE permission - but can read the script of the stored procedure Is there any SQL statement or helper function for these purpose? REFERENCE: - Granting execute permission on all stored procedures in a certain database

Original source

Related problems