Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Grant Execute Permissions to SQL Server Stored Procedures using DDL Triggers


By:   |   Last Updated: 2009-11-09   |   Comments (4)   |   Related Tips: More > Triggers

Problem

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.

Solution

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')
   DROP TRIGGER [DDLTRG_StoredProcedureCreate] ON DATABASE
GO

CREATE TRIGGER DDLTRG_StoredProcedureCreate
ON DATABASE
FOR Create_Procedure
/***************************************************************
* Purpose: Grant execute permissions to DevUserRole for all new stored procedures created.
*
* MODIFICATIONS
* 11-03-09 Ranga Narasimhan NEW
***************************************************************/
AS
DECLARE @data XML
DECLARE @objectname VARCHAR(255)
DECLARE @DatabaseName VARCHAR(255)
DECLARE @SchemaName VARCHAR(255)
DECLARE @strsql VARCHAR(500)
SET @data = EVENTDATA()
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.
BEGIN
   SET @strsql = 'grant execute on '[email protected]+'.'[email protected] +'.'[email protected]+' to DevUserRole'
   EXECUTE (@strsql)
END
GO

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:complexType name="EVENT_INSTANCE_CREATE_PROCEDURE">
   <xs:sequence>
      <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"/>
   <xs:sequence> 
<xs:complexType>

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

Next Steps


Last Updated: 2009-11-09


next webcast button


next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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.

 

Thomas


Monday, November 09, 2009 - 5:06:39 PM - Ranga Back To Top

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 09, 2009 - 4:02:36 PM - --cranfield Back To Top

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?

 

cheers


Monday, November 09, 2009 - 10:30:13 AM - apathetic Back To Top
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.

Learn more about SQL Server tools