Grant Execute Permissions to SQL Server Stored Procedures using DDL 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 '+@DatabaseName+'.'+@SchemaName +'.'+@objectname+' 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.

Source Collected from MSSQLTIPS.COM