[ACCEPTED]-MS SQL Server: Check to see if a user can execute a stored procedure-stored-procedures
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
0
Assuming the SP only runs a SELECT statement:
EXECUTE 9 AS USER = [User's ID/Login]
EXEC sp_foobar( sna, fu)
REVERT
It's 8 important to note that you will need to 7 run the REVERT command after the prompt 6 as SQL Server will regard you as the user 5 you are EXECUTING AS until you either shut 4 down the connection or REVERT the impersonation. That 3 said, you should see exactly what a user 2 would get (getting some rows but not all? This 1 should help you out).
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.