Finding stored procedures having execute permission

Question

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:

1. Granting execute permission on all stored procedures in a certain database

Solution 1

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

Solution 2

To check the permission for a different user, use this:

use my_db;
EXECUTE AS user = 'my_user'
SELECT SUSER_NAME(), USER_NAME();
select name, 
    has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute

from sys.procedures
where name = 'myprocname';
revert;

Works for my SQL Server 2012.

Solution 3

The answer from knb doesn't work for me because of missing rights. (a solution for a different user than the current one)

Cannot execute as the database principal because the principal "my user" does not exist, this type of principal cannot be impersonated, or you do not have permission.

This answer shows how to get the list of stored procedures on which a specific database user ('my user') has EXECUTE permission explicitly granted:

SELECT [name]
FROM sys.objects obj
INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id
WHERE obj.[type] = 'P' -- stored procedure
AND dp.permission_name = 'EXECUTE'
AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
AND dp.grantee_principal_id = 
    (SELECT principal_id
    FROM sys.database_principals 
    WHERE [name] = 'my user')

I modified it as follows to get the list I need:

SELECT [name]
FROM sys.procedures
WHERE [name] NOT IN
    (SELECT [name]
    FROM sys.objects obj
    INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id
    WHERE obj.[type] = 'P' -- stored procedure
    AND dp.permission_name = 'EXECUTE'
    AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
    AND dp.grantee_principal_id = 
        (SELECT principal_id
        FROM sys.database_principals 
        WHERE [name] = 'my user'))

Tested on Microsoft SQL Server 2008 R2

Solution 4

HAS_PERMS_BY_NAME, as used in the context of the script provided in the first answer, will provide the desired result only if you are connected as "MYUSER" since this function

"Evaluates the effective permission of the current user"

Solution 5

Extending on the accepted answer above, in order to check objects outside of the dbo schema, use the following statement.

  SELECT 
    name,
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'EXECUTE') AS has_execute,
    HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'VIEW DEFINITION') AS has_view_definition
  FROM sys.procedures

View more solutions

Comments

[LCJ]

I can view the script of the stored procedure using "Script Stored Procedure As"--> "Create To"; but the has_view_definition is coming as "0". How can we correct it?

[Remus Rusanu]

Can you run SELECT * FROM sys.fn_my_permissions('<procedure>', 'OBJECT') and post here the result?

[Remus Rusanu]

BTW, my query does not handle procedures in other schema, not dbo. You can easily fix that yourself.

[lloyd]

Works in 2008 R2