Post reply

user permission issue

  • 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

  • January 16, 2015 at 9:57 am

    #1770813

    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!

  • January 16, 2015 at 10:09 am

    #1770817

    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.

  • January 16, 2015 at 3:01 pm

    #1770911

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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