6

As the title suggests, I need to grant the execute permission on every stored procedure in a database. We've moved from test to production and have less control over the production database.. and all of the imported stored procedures got zero permissions right now. An interesting thing to know is if there is any way to make sure all of the imported stored procedures get execute permissions from the beginning?

Thanks for any help.

Phil
  • 3,934
  • 12
  • 38
  • 62

3 Answers3

10

As well as granting permissions, you should consider schemas.

So your stored procs are named in the Procs schema

  • Procs.DoStuff
  • Procs.DoMoreStuff
  • Procs.WriteStuff

Then you can GRANT EXECUTE ON SCHEMA::Procs TO RoleWhatever. All stored procedures created in Procs then inherit EXECUTE permissions

RoleWhatever could be a support role in production but a developer role in other environments.

gbn
  • 422,506
  • 82
  • 585
  • 676
7

Generate the statements, then copy them and paste into query window to run them

select 'grant execute on ' + 
    QuoteName(specific_schema) + '.' +
    QuoteName(specific_name) + ' to someone'
from information_schema.routines
where routine_type='PROCEDURE'
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • Could you explain what you mean by "generate the statements"? – Phil Feb 24 '11 at 19:46
  • 1
    Except that every time you add a stored procedure, you need to grant permissions again. This is useful when the following solution http://stackoverflow.com/a/5109747/224704 isn't possible/appropriate for whatever reason. – Disillusioned Jul 26 '12 at 09:26
  • This is quite clumsy really. Schemas allow you to GRANT on the schema and all new procs in that schema inherit the permissions – gbn Oct 31 '12 at 08:22
4

You have to grant the execute permission on the schema and repeat if you've got multiple schemas

grant  execute on schema :: yourschema to username
Bo Persson
  • 90,663
  • 31
  • 146
  • 203
Amila
  • 2,779
  • 1
  • 27
  • 31