En un entorno de desarrollo donde los permisos para los desarrolladores están restringidos, puede ser un desafío conceder derechos de ejecución cada vez que se genera un nuevo procedimiento almacenado. Sin embargo, hay una solución que te permite automatizar este proceso sin conceder permisos adicionales a los desarrolladores. En este artículo, exploraremos cómo utilizar desencadenadores DDL en SQL Server para conceder automáticamente permisos de ejecución para nuevos procedimientos almacenados.

¿Qué son los desencadenadores DDL?

Los desencadenadores DDL, disponibles en SQL Server 2005 y versiones posteriores, se activan cuando se realizan cambios en los objetos de la base de datos. Estos desencadenadores se pueden crear para ejecutarse cuando se crea una nueva tabla, un nuevo procedimiento almacenado y más. Al aprovechar los desencadenadores DDL, podemos automatizar el proceso de conceder permisos de ejecución para nuevos procedimientos almacenados.

Creación de un desencadenador DDL para el evento Create Procedure

Supongamos que tenemos un rol de base de datos llamado DevUserRole, y todos los desarrolladores son miembros de este rol. Este rol también es miembro de los roles de base de datos db_datareader y db_datawriter. Queremos conceder permisos de ejecución al rol DevUserRole cada vez que se crea un nuevo procedimiento almacenado. Aquí tienes un ejemplo de script que logra esto:

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
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'
BEGIN
   SET @strsql = 'GRANT EXECUTE ON '+@DatabaseName+'.'+@SchemaName +'.'+@objectname+' TO DevUserRole'
   EXECUTE (@strsql)
END
GO

En el código anterior, creamos un desencadenador DDL llamado DDLTRG_StoredProcedureCreate en la base de datos para el evento Create_Procedure. El desencadenador utiliza la función EVENTDATA() para recuperar información sobre el evento. Extraemos el nombre del objeto, el nombre de la base de datos y el nombre del esquema de los datos del evento y comprobamos si el nombre del esquema es ‘dbo’. Si lo es, construimos una declaración SQL dinámica para conceder permisos de ejecución al rol DevUserRole para el nuevo procedimiento almacenado creado.

Comprensión de la función EVENTDATA()

La función EVENTDATA() devuelve un valor de tipo XML, que contiene información sobre el evento. De forma predeterminada, la definición del esquema para todos los eventos se instala en el siguiente directorio: C:\Program Files\Microsoft SQL Server\{versión}\Tools\Binn\schemas\sqlserver\{año}\{mes}\events\events.xsd. Puedes consultar esta definición de esquema para comprender la estructura de los datos del evento.

Para el evento Create_Procedure, la función EVENTDATA() proporciona la siguiente información:

<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>

Con esta información, podemos extraer fácilmente los detalles necesarios para conceder permisos de ejecución para el nuevo procedimiento almacenado.

Conclusión

Al utilizar desencadenadores DDL en SQL Server, podemos automatizar el proceso de conceder permisos de ejecución para nuevos procedimientos almacenados. Esto permite a los desarrolladores ejecutar los procedimientos almacenados sin necesidad de permisos adicionales. Al comprender la estructura de los datos del evento proporcionados por la función EVENTDATA(), podemos extraer la información necesaria para realizar las acciones deseadas. La implementación de esta solución puede simplificar en gran medida la gestión de permisos en un entorno de desarrollo.