MS SQL Server: Check to see if a user can execute a stored procedure
Try something like this:
CREATE PROCEDURE [dbo].[sp_canexecute]@procedure_name varchar(255),@username varchar(255),@has_execute_permissions bit OUTPUTASIF 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 = 1ENDELSEBEGIN SET @has_execute_permissions = 0ENDGO
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).