Andrew Andrew - 7 months ago 24
SQL Question

MS SQL Server: Check to see if a user can execute a stored procedure

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.

Answer

fn_my_permissions and HAS_PERMS_BY_NAME

eg.

EXECUTE AS USER = 'username';
SELECT * FROM fn_my_permissions('[storedProcedureSchema].[storedProcedureName]', 'OBJECT') 
    where permission_name = 'EXECUTE'
    ORDER BY subentity_name, permission_name ;  
REVERT;