Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: exec permision for sql user

    I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:
    Code:
    select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
    from INFORMATION_SCHEMA.ROUTINES

  3. #3
    Join Date
    Apr 2003
    Posts
    176
    hey, thanks for the information.
    what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •