Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers

By:   |   Comments (4)   |   Related: > Triggers


In your development environment if you have locked down permissions for developers, but still need them to execute stored procedures you will need to grant execute rights each time a new stored procedure is generated. In this tip I will show you a way of doing this automatically each time a new stored procedure is created without granting your developers additional permissions.


The simple way that this can be accomplished is by using DDL triggers which are available in SQL Server 2005 and later versions.

Data Definition Language (DDL) triggers are fired when changes are made to database objects. For example, DDL triggers can be created to execute when a new table is created or a new stored procedure is created etc. For a whole list of events you can run this query.

select * from sys.trigger_event_types  

Create a DDL trigger on Create Procedure event

In my environment there is a database role called DevUserRole. All developers are members of this role. This role is a member of db_datareader and db_datawriter database roles. I had executed a script to grant execute permissions on all the existing stored procedures, but I wanted a way to do this automatically for all new stored procedures.

Here is the script that I created to do this. This uses a DDL trigger that is fired for the "Create_Procedure" event. In this script I will grant execute rights to the role DevUserRole whenever a new stored procedure is created. I am also limiting this to only do this when the object is part of the "dbo" schema.


To get a list of all available events: 
select * from sys.trigger_event_types 

This proc will grant execute permissions for any new procedure that is created.

Change the Rolename in this script for your environment.


IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'DDLTRG_StoredProcedureCreate')

FOR Create_Procedure
* Purpose: Grant execute permissions to DevUserRole for all new stored procedures created.
* 11-03-09 Ranga Narasimhan NEW
DECLARE @objectname VARCHAR(255)
DECLARE @DatabaseName VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @strsql VARCHAR(500)
SET @objectname = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)')
SET @DatabaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')
SET @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(256)')
IF @SchemaName = 'DBO' --Grant execute permissions for stored procedures owned by dbo schema.
   SET @strsql = 'grant execute on '+@DatabaseName+'.'+@SchemaName +'.'+@objectname+' to DevUserRole'
   EXECUTE (@strsql)

As you can see in the above code I am using EVENTDATA which returns a value of type xml. By default, the schema definition for all events is installed in the following directory: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.

Here is a list of the information that is available in the EVENTDATA() function for the Create_Procedure event.

      <xs:element name="EventType" type="SSWNAMEType"/>
      <xs:element name="PostTime" type="xs:string"/>
      <xs:element name="SPID" type="xs:int"/>
      <xs:element name="ServerName" type="PathType"/>
      <xs:element name="LoginName" type="SSWNAMEType"/>
      <xs:element name="UserName" type="SSWNAMEType"/>
      <xs:element name="DatabaseName" type="SSWNAMEType" />
      <xs:element name="SchemaName" type="SSWNAMEType" />
      <xs:element name="ObjectName" type="SSWNAMEType" />
      <xs:element name="ObjectType" type="SSWNAMEType" />
      <xs:element name="TSQLCommand" type="EventTag_TSQLCommand"/>

As you can see this is a pretty easy process to put in place each time a new procedure is created.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ranga Narasimhan Ranga Narasimhan has been in IT for over 10 years working on Siebel, Visual Basic, Crystal Reports and SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, November 13, 2009 - 10:05:42 AM - ThomasLL Back To Top (4429)

Why would you grant developers execute permission in a production database?

 We have Functions-UDFs (that do not update data) for them to run if they need info. But never do we let them execute SPs in production that could change data.



Monday, November 9, 2009 - 5:06:39 PM - Ranga Back To Top (4400)

Not sure what you are asking, but all members of the database Role will get the exec permissions if you just grant exec permissions for a database role.

Monday, November 9, 2009 - 4:02:36 PM - --cranfield Back To Top (4399)

nice tip. thanks. Its cools what you can do with triggers.

Tell me - If you created a database role and granted EXEC to that role and then added the devgroup to that role would that have the same effect as automatically granting EXEC to devgroup each time a new proc was created in the database?



Monday, November 9, 2009 - 10:30:13 AM - apathetic Back To Top (4395)
You could also just grant EXECUTE permission on the dbo schema. This will also grant the ability to execute scalar functions, which may or may not be what you want.

get free sql tips
agree to terms