La Guía Definitiva para Otorgar Permisos en Todas las Tablas de Usuario en SQL Server

Gestionar permisos en SQL Server es crucial para mantener la integridad y seguridad de tu base de datos. Un escenario común surge cuando necesitas otorgar permisos en todas las tablas de usuario a un rol específico de la base de datos sin inconvenientes. Si te sientes frustrado por tener que escribir múltiples scripts o ajustar manualmente permisos cada vez que agregas nuevas tablas, no estás solo. En esta guía, exploraremos cómo crear un script SQL versátil que te permita otorgar permisos de SELECT, REFERENCES, INSERT, UPDATE y DELETE a un rol de base de datos en todas las tablas de usuario con facilidad.

El Desafío: Otorgar Permisos de Manera Eficiente

SQL Server Management Studio (SSMS) genera típicamente scripts para objetos de base de datos individuales, lo que puede ser tedioso al tratar con múltiples tablas. Además, modificar permisos para tablas recién agregadas puede convertirse rápidamente en una tarea que consume mucho tiempo. Lo que necesitas es un script robusto de “fire-and-forget” que pueda ejecutarse para aplicar permisos en todas las tablas de usuario sin necesidad de volver a visitar el script cada vez.

La Solución: Un Procedimiento Almacenado para Otorgar Permisos

Para automatizar eficazmente la concesión de permisos, podemos crear un procedimiento almacenado que emplee un cursor para recorrer todos los objetos de usuario y otorgar los permisos deseados. Vamos a desglosar el proceso en pasos manejables:

Paso 1: Crear el Procedimiento Almacenado

Comienza definiendo tu procedimiento almacenado. Usa el siguiente código SQL como plantilla:

IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO

CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30),
 @time VARCHAR(8),
 @rights VARCHAR(20),
 @role VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT name
    FROM sysobjects
    WHERE type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN
    SELECT @rights = 'ALL',
           @role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] accediendo al objeto %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)
    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO

GRANT ALL ON sp_grantastic TO PUBLIC
GO

Paso 2: Entender los Componentes

• Declaración de Cursor: El cursor c_objects se configura para recorrer todas las tablas de usuario (U), vistas (V) y procedimientos almacenados (P).
• Ejecución de Grants: Para cada objeto recuperado a través del cursor, el sistema ejecuta un comando GRANT, aplicando los derechos definidos al rol especificado.
• Información de Salida: El procedimiento imprime registros que indican la hora de ejecución y cualquier objeto al que se le hayan otorgado permisos.

Paso 3: Ejecutar el Procedimiento

Una vez creado el procedimiento almacenado, puedes ejecutarlo con un simple comando:

EXEC sp_grantastic

Con solo este comando, tu rol de base de datos recibirá los permisos necesarios en todas las tablas de usuario sin problemas.

Conclusión

Acabas de aprender a crear un poderoso script SQL que otorga permisos a un rol de base de datos en todas las tablas de usuario de manera sencilla. Este enfoque no solo optimiza tu flujo de trabajo, sino que también asegura que tus permisos estén siempre actualizados sin intervención manual. La próxima vez que agregues una nueva tabla a tu base de datos, simplemente ejecuta el procedimiento almacenado nuevamente, y estarás listo para continuar.

¡Feliz codificación!