2

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?

3

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
  • 1
    Thanks, that helped a lot! My update scripts had got mixed up so the permissions kept getting removed without me noticing. – Mourndark Sep 22 '16 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.