9

How can you check to see if a user can execute a stored procedure in MS SQL server?

I can see if the user has explicit execute permissions by connecting to the master database and executing:

databasename..sp_helpprotect 'storedProcedureName', 'username'

however if the user is a member of a role that has execute permissions sp_helprotect won't help me.

Ideally I'd like to be able to call something like 

databasename..sp_canexecute 'storedProcedureName', 'username'

which would return a bool.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Andrew
  • 7,286
  • 3
  • 28
  • 38
  • Also, if you're using SQL Server 2005 or above and the permission is assigned to the schema or the database (EXECUTE can be now), sp_helprotect won't report it. The stored procedure is included for backward compatibility only and reports permissions based on what was in SQL Server 2000. – K. Brian Kelley Jan 27 '09 at 19:18

3 Answers3

14

fn_my_permissions and HAS_PERMS_BY_NAME

Box Box Box Box
  • 5,094
  • 10
  • 49
  • 67
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • 2
    I don't think these functions answer the original question, which was to check whether **_any given user_** has execute permissions on a given stored proc. The MSDN articles for both functions say they return permissions for the **_calling principal only_**, rather than any principal. – Simon Elms Mar 21 '17 at 20:33
4

Try something like this:

CREATE PROCEDURE [dbo].[sp_canexecute]
@procedure_name varchar(255),
@username varchar(255),
@has_execute_permissions bit OUTPUT
AS

IF EXISTS (
        /* Explicit permission */
        SELECT 1
        FROM sys.database_permissions p
        INNER JOIN sys.all_objects o ON p.major_id = o.[object_id] AND o.[name] = @procedure_name
        INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND dp.[name] = @username
    )
    OR EXISTS (
        /* Role-based permission */
        SELECT 1
        FROM sys.database_permissions p
        INNER JOIN sys.all_objects o ON p.major_id = o.[object_id]
        INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND o.[name] = @procedure_name
        INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
        INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id AND dp2.[name] = @username
    )
BEGIN
    SET @has_execute_permissions = 1
END
ELSE
BEGIN
    SET @has_execute_permissions = 0
END
GO
Dane
  • 9,737
  • 5
  • 28
  • 23
  • Does that work if the user is a member of a role that has permissions, and has not been explicitly allowed to execute the stored procedure? – Andrew Jan 28 '09 at 09:24
  • No, but you could put the role name in the @username parameter and that would still return the proper/expected boolean result. – Dane Jan 28 '09 at 22:34
  • Ok, I've updated the proc code above to take permissions granted by way of roles as well as explicit granting. – Dane Jan 28 '09 at 22:56
  • I don't think this takes into account permissions granted via parent permissions, like if the user has execute permissions for that whole schema, but not directly on that item. Is that accurate? – Maslow Feb 18 '14 at 20:58
1

Assuming the SP only runs a SELECT statement:

EXECUTE AS USER = [User's ID/Login]
EXEC sp_foobar( sna, fu)
REVERT

It's important to note that you will need to run the REVERT command after the prompt as SQL Server will regard you as the user you are EXECUTING AS until you either shut down the connection or REVERT the impersonation. That said, you should see exactly what a user would get (getting some rows but not all? This should help you out).

Pulsehead
  • 5,050
  • 9
  • 33
  • 37