user permission issue

Question

Post reply

user permission issue

Guras

SSCertifiable

Points: 6518
More actions
January 16, 2015 at 9:36 am

#302393

Here are the following steps I have undertaken
1.added user1 in db1
2.assigned the default schema as dbo
3.added him to the db_ddladmin role (so that he can create, alter, drop objects)
However he cannot grant EXECUTE permission to other users on a created stored procedure by him.
When he runs GRANT EXCUTE on proc1 to GRP1
The statement fails saying proc1 not found.
What is the additional step that I am missing?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

to grant permissions to other users, your user also needs the db_securityadmin priviledge

scroll down to "Permissions" on this page, and it's got the gory details:

http://msdn.microsoft.com/en-us/library/ms188371.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Guras SSCertifiable Points: 6518 More actions · Answer 2

Thank you that was it the user1 can grant permission to other users now. However we are running into another problem where the GRP1 cannot find the procs created by user1.

User1 has granted execute permission to grp1 on proc1.

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

You really shouldn't need full securityadmin just to grant EXECUTE on a proc(s):

GRANT EXECUTE TO user1 WITH GRANT OPTION

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".