Post reply

Grant EXECUTE to user for ALL stored procedures (existing & new) in a schema.

  • June 6, 2011 at 12:05 pm

    #389666

    Is there a way to grant the EXECUTE permission to a role/user for ALL stored procedures under a specific schema without granting them to each individually as they are created? I want to grant the permissions for the application user for all existing stored procedures and any NEW ones that are created in the future as in enhancements.

    If there is a way, is it bad practice to do so?

    I found this article, however there is no code under the link.

    http://qa.sqlservercentral.com/articles/DTS/grantingexecuteaccesstoallstoredprocedurestoagiven/2164/

    Thanks!

  • June 6, 2011 at 12:09 pm

    #1334599

    for the default schema... if you leave out the specific objectname it it grants to all objects:

    GRANT EXECUTE TO [YourRoleName]

    i think for a specific, non-default schema, say QA schema i think you might be right, that you have to grant by object...lemme test and confirm.

    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!

  • June 6, 2011 at 12:18 pm

    #1334603

    GRANT EXECUTE ON schema::<schema name> TO <user name>

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 6, 2011 at 12:21 pm

    #1334605

    doh Gail beat me to it..i found the example in my snippets:

    GRANT EXECUTE ON Schema::schemaname TO <RoleName>

    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!

  • June 6, 2011 at 12:38 pm

    #1334612

    Thank you, Lowell & Gila!! 😀

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

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