Post reply

Grant execute permission on all user defined stored procedures having a database

  • November 2, 2011 at 7:06 am

    #246170

    Hi;

    I want to grant execute permission on all user defined stored procedures of database.not on system store procedures of database.

    when we create/restore a database, it create store procesdure in 2 category

    1 - system store procedures

    2 - store procedures other than system SP. (created by developer or programer)

    I have an DB ID and want to grant execute permission on only 2 type of store procedures by using SQL query.

    do we have any query to grant execute permission on all user define sp (other than system sp) on a particular database.

  • November 2, 2011 at 9:05 am

    #1402204

    If you want to cover them all and you have SQL Server 2005 and higher as your SQL Server version, then GRANT EXECUTE against the appropriate schema (usually dbo). For instance:

    GRANT EXECUTE ON SCHEMA::dbo TO SomeDBRole;

    K. Brian Kelley
    @kbriankelley

  • November 3, 2011 at 3:45 am

    #1402545

    Hi;

    Thanks for the reply, but let me further define my target.

    I have attached a snap shot of my database (please review)

    in this picture I have a folder "system stored procedures" having many default system store procedures, I dont want to give permission to DB ID for any system store procedures

    in picture we also have some store procedures (out side the folder of system store procedures) I want to give execute permission to DB ID only for these store procedures.

    I hope it will clarify my objective, Thanks

  • November 3, 2011 at 3:48 am

    #1402548

    in addition to abbove, DB ID having only read & write permission to database. can not grant dbo access.

  • November 3, 2011 at 4:22 am

    #1402562

    I think you have three options, then:

    (1) Do as Brian suggested, but then explicitly deny EXECUTE on all system stored procedures.

    (2) Move your user-defined stored procedures to a different schema and grant EXECUTE on that

    (3) Grant EXECUTE individually on user stored procedures. This has the disadvantage that you have to remember to do this on every new proc that you create after that. You can generate your own script to do this:

    SELECT 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO <UserOrRole>'

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_TYPE = 'PROCEDURE'

    John

  • November 3, 2011 at 6:53 am

    #1402604

    Thanks you so much, I am abe to acheive my target by the help of your query.

    Thanks Take Care

Viewing 6 posts - 1 through 5 (of 5 total)

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