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
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?