question

seeloff avatar image
seeloff asked

user cannot grant execute on procedure user just created

the sequence below has generally been providing the expected access for developers USE [{dbname}] GO CREATE Role Development GO sp_addrolemember 'Development', '{domain}\{domaingroup}' -- valid server login and database user GO -- *grant permission to developers to access vendor supplies objects* GRANT SELECT, EXECUTE, VIEW DEFINITION, REFERENCES ON SCHEMA::dbo TO Development GO -- *grant developers the ability to create programming objects* GRANT CREATE PROCEDURE TO Development GO GRANT CREATE FUNCTION TO Development GO GRANT SHOWPLAN TO Development GO -- *grant developers full access to a schema to hold all their objects* GRANT SELECT, EXECUTE, VIEW DEFINITION, REFERENCES, INSERT, DELETE, UPDATE, ALTER ON SCHEMA::scmis TO Development GO -- ***however, I don't understand the sequence below*** *connect with windows authentication as member of {domain}\{domaingroup}* CREATE PROCEDURE scmis.xxx GO *check that procedure does exist – it does* GRANT EXECUTE ON scmis.xxx TO {other role} Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'xxx', because it does not exist or you do not have permission.123 ***I'm missing something.***
permissions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seeloff avatar image seeloff commented ·
The problem is that the dev guys can create procs - but not GRANT execute permissions to them. I tried the WITH GRANT option after posting the question, but it did not work as expected. I added the CONTROL permission on the schema, and has solved the problem stated. I'm still researching to make sure I didn't give away the farm with that permissions - but I think I'm ok giving that permission only to the schema. Thanks for your assistance working this out.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Granting "CREATE PROCEDURE" permissions does just that - it allows the user to create procedures within the database. It does not automatically confer the right to grant execute permissions on that procedure. Looking at the [detailed GRANT][1] statement options shows that the "CREATE PROCEDURE" is implied by having "ALTER" access to the server; and that "EXECUTE" is implied by having "CONTROL" access to the server. The interesting bit is that "ALTER" is implied by having "CONTROL" access. This isn't perhaps the clearest explanation of what's going on, but it seems to me that it requires higher privs on the database to be allowed to grant permissions than it is to be able to create objects... Perhaps you need to run: GRANT EXECUTE ON SCHEMA::scmis TO DEVELOPMENT in order to short-circuit this and allow all developers to execute all SPs. EDIT: - Re-reading the question, it's possible this paragraph holds the answer: > "Object owners can grant permissions on the objects they own. Principals that have CONTROL permission on a securable can grant permission on that securable." Do the DEVELOPERS own the scmis schema and the objects therein? [1]: http://msdn.microsoft.com/en-us/library/ms178569.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You need to alter your code to give 'WITH GRANT' permissions. [ http://msdn.microsoft.com/en-us/library/ms187965.aspx][1] gives details but essentially, you can give permission to someone to execute and as a separate securable option you can give someone permission to give someone else permission. GRANT EXECUTE ON my_stored_proc to [MyDomain\A_Developer] WITH GRANT gives the developer permission to execute the procedure and also the permission to grant execute permission to other users ![alt text][2] [EDIT] - after your new comment. Do the developer own the schema in which they are creating the procedures? Can they create procedures in the dbo schema and GRANT EXECUTE on them? [1]: http://msdn.microsoft.com/en-us/library/ms187965.aspx [2]: /upfiles/permissions.png
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I thought about that, and then I decided it didn't match the problem as stated - that the dev guys can create procs but not execute them...
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
so I have missed something too then ... poss their default schema is confusing things?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
No, I've missed it as well. I've just re-read the question, and my answer's shash. Hmm.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.