Home > SQL Server > SQL Script to GRANT EXECUTE Permissions to all Stored Procedures

SQL Script to GRANT EXECUTE Permissions to all Stored Procedures

Well I had a task on one of my projects to GRANT EXECUTE rights to all stored procedures to a certain database role I had set up and I wanted this to be scripted. So I thought we must be able to build up some nice SQL script using the select command ….. and you can!
Below is an example script you can past into SQL Management Studio (SQL Server 2005) or Query Analyser (SQL Server 2000) which will build the T-SQL required for scripting.  You will need to change the text YellowDuckGuyRole to your role you have created (create a role if you don’t have one).  Type ‘P’ stands for Stored Procedures from sys.objects.
select   ‘GRANT EXECUTE ON dbo.’ + name + ‘ TO YellowDuckGuyRole’
from     sys.objects
where  type  =  ‘P’
order by  name
Once you have the output in SQL Server then you can save the results as you like i.e. to text file or simply copy the results and paste into notepad – easy!
Yellow Duck Guy
Greg Olsen
Categories: SQL Server
  1. Mark
    February 22, 2012 at 7:10 am

    I often start this with something like ‘SELECT DISTINCT type,type_desc FROM sys.objects’
    and from there tweak a query like yours

    Its rare that I am just interested in sp’s.

  2. December 2, 2013 at 10:21 am

    Gidday Greg,

    I’ve used your script heaps of times, so thankyou very much for posting. Today I needed to grant execute to a bunch of stored procedures with different schemas. Here’s an updated version of your script that handles procs with a schema other than dbo:

    select ‘GRANT EXECUTE ON ‘ +sys.schemas.name + ‘.’ + sys.objects.name + ‘ TO YellowDuckGuyRole’
    from sys.objects
    INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
    where type = ‘P’
    order by sys.objects.name

    • December 10, 2013 at 4:01 pm

      Thanks Adam. Glad the script has helped. Thanks also for the updated script.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: