CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2008-r2
LCJ
LCJ

Reputation: 22652

Finding stored procedures having execute permission

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

Upvotes: 15

Views: 63401

Answers (5)

rschoenbach
rschoenbach

Reputation: 535

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

Upvotes: 3

ofthelit
ofthelit

Reputation: 1491

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

Upvotes: 10

knb
knb

Reputation: 9285

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.

Upvotes: 18

jhenrichs
jhenrichs

Reputation: 51

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"

Upvotes: 5

Remus Rusanu
Remus Rusanu

Reputation: 294187

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

Upvotes: 19

Related Questions