SQL Server 2012 – User Can’t Execute Stored Procedure Despite Role Permission

rolesql serversql-server-2012stored-procedures

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?

Best Answer

this script gives the permissions assigned to a table or stored procedure in a script format.

have a look what it returns for your MyProcedure that could be a start.

SELECT
  (
    dp.state_desc + ' ' +
    dp.permission_name collate latin1_general_cs_as + 
    ' ON ' + '[' + s.name + ']' + '.' + '[' + o.name + ']' +
    ' TO ' + '[' + dpr.name + ']'
  ) AS GRANT_STMT
FROM sys.database_permissions AS dp
  INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
  INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
  INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
WHERE 1=1
    AND o.name IN ('MyProcedure')      -- Uncomment to filter to specific object(s)
--  AND dp.permission_name='EXECUTE'    -- Uncomment to filter to just the EXECUTEs
ORDER BY dpr.name
Related Question