November 21, 2008 at 12:16 pm
I am attempting to allow people to create stored procedures and then GRANT EXECUTE on those procedures to others.
The group (a domain group) has been granted EXECUTE permissions, WITH GRANT OPTION on the schema in which their procedures are to be created.
Assuming that the schema is dbo, and that both MyDomain\MyDomainGroup and MyDomain\MyOtherDomainAccount exist as database principals to reproduce the situation:
IF EXISTS (SELECT * FROM sys.objects where name = 'PermissionsTestProc' AND type = 'P' AND schema_id = 1)
BEGIN
DROP PROCEDURE dbo.PermissionsTestProc
END
GO
CREATE PROCEDURE dbo.PermissionsTestProc
AS
Select suser_sname()
GO
GRANT EXECUTE on SCHEMA::dbo TO [MyDomain\MyDomainGroup] WITH GRANT OPTION
GO
/***********************************************/
Runnning:
SELECT p.name, m.*
FROM sys.database_permissions m
INNER JOIN sys.database_principals p ON m.grantee_principal_id = p.principal_id
WHERE state = 'W'
shows that MyDomain\MyDomainGroup has grant permissions on the schema.
A member of MyDomain\MyDomainGroup can EXEC dbo.PermissionsTestProc, and get the expected results.
HOWEVER . . .
When that same member of MyDomain\MyDomainGroup tries to GRANT EXECUTE on the procedure in that schema, using:
GRANT EXECUTE ON dbo.PermissionsTestProc
TO [mydomain\MyOtherDomainAccount] AS [MyDomain\MyDomainGroup]
The grant fails with the following error message:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'PermissionsTestProc', because it does not exist or you do not have permission.
I would have thought that their having EXEC WITH GRANT on the entire schema would have allowed them to GRANT EXEC on all procedures in that schema, but that appears not to work. Any information or advice would be appreciated.
December 4, 2008 at 12:56 pm
Ben - have you found a fix for this? I ask because I have a similar problem. I can create and alter a procedure, but don't have the right to execute it.
December 23, 2008 at 8:44 am
I am seeing the same problem
February 17, 2010 at 1:06 pm
So far I have not been able to resolve this directly. I have found that if the WITH GRANT OPTION is granted to an individual acount as opposed to a domain group, then it APPEARS to work as expected. It just seems a bit of a step backward. We can grant access to a development group, but have to create and manage individual accounts to allow them to GRANT EXEC on the procs they create or alter. We would not want that in test or highter environments, but isn't def where they are SUPPOSED to be able to develope and check?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply