Asignar permisos a un rol masivamente en SQL Server

Hola a todos, en el día a día, siempre hay algunas tareas que se repiten y tienen un guión. Pero ese guión, alguna vez puede fallar y se quede algún paso en el camino. Normalmente no pasa nada hasta que empiezan a llegar incidencias. En esta ocasión os vamos a compartir un script para asignar permisos a un rol masivamente.

¿Qué hace el script?

Este script se puede ejecutar con seguridad, ya que:

• NO modifica permisos.
• Sólo identifica vistas sin GRANT SELECT explícito al rol.
• Genera el comando GRANT correspondiente.

Por tanto este script realmente comprueba si el rol que queremos, tiene permisos sobre las vistas. En caso de encontrar alguna vista que falte, genera el script para asignar permisos sobre esa vista. Finalmente te indica un resumen de cuantas vistas por BBDD, le falta los permisos. Para este ejemplo usaremos el rol «RolLectura«.

/* =====================================================================
   REVISION DE PERMISOS SELECT SOBRE VISTAS
   ===================================================================== */

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#VistasSinPermiso') IS NOT NULL
    DROP TABLE #VistasSinPermiso;

CREATE TABLE #VistasSinPermiso
(
    DatabaseName   sysname,
    SchemaName     sysname,
    ViewName       sysname,
    RoleExists     varchar(2),
    GrantCommand   nvarchar(max)
);

DECLARE
    @DatabaseName sysname,
    @SQL          nvarchar(max);

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
  AND database_id > 4
ORDER BY name;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = N'
    USE ' + QUOTENAME(@DatabaseName) + N';

    IF EXISTS
    (
        SELECT 1
        FROM sys.database_principals
        WHERE name = N''RolLectura''
          AND type = ''R''
    )
    BEGIN

        INSERT INTO #VistasSinPermiso
        (
            DatabaseName,
            SchemaName,
            ViewName,
            RoleExists,
            GrantCommand
        )
        SELECT
            DB_NAME(),
            s.name,
            v.name,
            ''SI'',
            ''USE '' + QUOTENAME(DB_NAME()) + '';''
            + CHAR(13) + CHAR(10)
            + ''GRANT SELECT ON ''
            + QUOTENAME(s.name)
            + ''.''
            + QUOTENAME(v.name)
            + '' TO [RolLectura];''
        FROM sys.views v
        INNER JOIN sys.schemas s
            ON s.schema_id = v.schema_id
        WHERE v.is_ms_shipped = 0

          AND NOT EXISTS
          (
              SELECT 1
              FROM sys.database_permissions dp
              INNER JOIN sys.database_principals p
                  ON p.principal_id = dp.grantee_principal_id
              WHERE p.name = N''RolLectura''
                AND dp.class = 1
                AND dp.major_id = v.object_id
                AND dp.minor_id = 0
                AND dp.permission_name = ''SELECT''
                AND dp.state IN (''G'', ''W'')
          );

    END
    ELSE
    BEGIN

        INSERT INTO #VistasSinPermiso
        (
            DatabaseName,
            SchemaName,
            ViewName,
            RoleExists,
            GrantCommand
        )
        VALUES
        (
            DB_NAME(),
            NULL,
            NULL,
            ''NO'',
            ''-- El rol [RolLectura] no existe en '' + QUOTENAME(DB_NAME())
        );

    END;
    ';

    BEGIN TRY
        EXEC sys.sp_executesql @SQL;
    END TRY
    BEGIN CATCH

        PRINT 'Error revisando ' + QUOTENAME(@DatabaseName)
            + ': ' + ERROR_MESSAGE();

    END CATCH;

    FETCH NEXT FROM db_cursor INTO @DatabaseName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;


/* =====================================================================
   RESULTADO
   ===================================================================== */

SELECT
    DatabaseName AS [BBDD],
    SchemaName   AS [Schema],
    ViewName     AS [Vista],
    RoleExists   AS [Existe rol RolLectura],
    GrantCommand AS [GRANT a ejecutar]
FROM #VistasSinPermiso
ORDER BY
    DatabaseName,
    SchemaName,
    ViewName;


/* =====================================================================
   RESUMEN POR BASE DE DATOS
   ===================================================================== */

SELECT
    DatabaseName AS [BBDD],
    COUNT(CASE WHEN ViewName IS NOT NULL THEN 1 END) AS [Vistas sin GRANT SELECT]
FROM #VistasSinPermiso
GROUP BY DatabaseName
ORDER BY DatabaseName;

Tras ejecutarlo, nos devolverá un script similar a este por cada vista, que tendríamos que ejecutar a mano posteriormente para aplicarlo:

USE [BBDD];  GRANT SELECT ON [dbo].[vista_sin_permisos] TO [RolLectura];

Con esto conseguiremos adelantarnos a futuras posibles incidencias y revisar si queda alguna vista más por corregir.

Asignar permisos de manera profesional

La asignación de permisos y el control de los mismos es una labor importante de un DBA. El descontrol sobre estos puede repercutir en la seguridad de toda la empresa y sus clientes. Por eso es importante contar con expertos DBAs. Tenemos una sólida experiencia en SQL Server y más bases de datos. Delega tus bases de datos en profesionales con nuestro servicio de Soporte y Mantenimiento de SQL Server. Contáctanos sin compromiso para evaluar tu caso y ver cómo podemos ayudarte con tu entorno de bases de datos.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn