CODEWITHSUNDEEP
sql-serverstored-procedurespermissions
Phil
Phil

Reputation: 3994

Granting execute permission on all stored procedures in a certain database

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.

Upvotes: 6

Views: 18907

Answers (3)

gbn
gbn

Reputation: 432261

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.

Upvotes: 10

Amila
Amila

Reputation: 2819

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

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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'

Upvotes: 7

Related Questions