I'm setting up a number of stored procedures and I would like to limit the users who can execute them to a specific server role. I've created my role and users like this:
USE [MyDB]
GO
CREATE ROLE [MyExecuters]
GO
CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [MyExecuters] ADD MEMBER [MyUser]
GO
And I've granted permissions on the SP to the role like this:
USE [MyDB]
GO
GRANT EXECUTE ON [dbo].[MyProcedure] TO [MyExecuters] AS [dbo];
GO
However if MyUser
tries to execute the SP, they get an access denied message. I tried explicitly granting the EXECUTE
permission on the SP to MyUser
(through the procedure properties in Management Studio) and now they can execute it fine. What's going on?
EXECUTE AS
clause in stored procedure declaration ?