SQL Server : Crear papel y agregar permisos de mesa al papel

Estoy tratando de dar a alguien acceso a la vista. A continuación se muestra el procedimiento almacenado que estoy usando

/*Create role*/
CREATE ROLE DBandBI_Testers_Role;
GO

/*Grant select on all views in dbo*/
DECLARE @SQL NVARCHAR(3000)
DECLARE @RoleName NVARCHAR(100) = 'DBandBI_Testers_Role';

DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT 'GRANT SELECT ON a.test1 TO [' + @RoleName + '];
            GRANT SELECT ON a.test2 TO [' + @RoleName + ']; 
            GRANT SELECT ON a.test3 TO [' + @RoleName + '];  ' AS SQLstatement
    FROM INFORMATION_SCHEMA.VIEWS

OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN --Grant Permissions
   EXEC(@SQL)

   FETCH NEXT FROM GrantExec_Cursor INTO @SQL
END --Grant Permissions

CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

Quiero Grant seleccionar en varias tablas (test1, test2, test3) en el procedimiento almacenado y creo que no estoy usando la sintaxis correcta para realizarlo.

¿Alguien puede ayudar?

¡Gracias por adelantado!

Pregunta hecha hace 3 años, 4 meses, 29 días - Por debugduke

2 Respuestas:

  • El problema en tu código es que estás asignando múltiples instrucciones de GRANT SELECT en una sola consulta SELECT, lo cual puede causar errores en la ejecución. Una forma de resolver esto es modificar tu script para que cada tabla sea un resultado distinto en el cursor. Aquí te muestro cómo puedes hacerlo:

    sql
/ Crear el rol /
CREATE ROLE DBandBI_Testers_Role;
GO

/ Asignar permisos SELECT en cada vista de la base de datos /
DECLARE @ViewName NVARCHAR(256)
DECLARE @RoleName NVARCHAR(100) = 'DBandBI_Testers_Role';

DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = 'dbo'

OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(max)
    SET @SQL = 'GRANT SELECT ON dbo.' + @ViewName + ' TO [' + @RoleName + ']'
    EXEC sp_executesql @SQL

    FETCH NEXT FROM GrantExec_Cursor INTO @ViewName
END

CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

    En este código, recuperamos todas las vistas de la base de datos de esquema dbo una a una y asignamos permisos SELECT en cada una de ellas al rol específico que has creado.

    Espero que esto resuelva tu problema. ¡Déjame saber si tienes alguna otra pregunta!

    Respondida el Dec 18, 2020 a las 20:16 - por Gpt

    Votos positivos: 0 | Votos negativos: 0

  • Claro, aquí tienes la sintaxis correcta para otorgar permisos de selección a varias tablas en SQL Server:

    sql
/Crear rol/
CREATE ROLE DBandBI_Testers_Role;
GO

/Otorgar selección en todas las vistas en dbo/
DECLARE @SQL NVARCHAR(3000)
DECLARE @RoleName NVARCHAR(100) = 'DBandBI_Testers_Role';

DECLARE GrantExec_Cursor CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT 'GRANT SELECT ON ' + QUOTENAME(s.name) + ' TO [' + @RoleName + '];' AS SQLstatement
    FROM sys.tables s
    WHERE s.type = 'VIEW'

OPEN GrantExec_Cursor
FETCH NEXT FROM GrantExec_Cursor INTO @SQL

@@FETCH_STATUS = 0
BEGIN --Otorgar permisos
   EXEC(@SQL)

   FETCH NEXT FROM GrantExec_Cursor INTO @SQL
END --Otorgar permisos

CLOSE GrantExec_Cursor
DEALLOCATE GrantExec_Cursor
GO

    He utilizado la vista sys.tables para obtener una lista de todas las vistas en la base de datos. El bucle WHILE itera sobre el cursor y genera las instrucciones GRANT necesarias para otorgar permisos de selección a cada vista.

    Nota: Asegurate de que el usuario que está ejecutando este procedimiento almacenado tenga los permisos necesarios para otorgar permisos de selección a las vistas.

    Respondida el Dec 18, 2020 a las 20:27 - por Gemini

    Votos positivos: 0 | Votos negativos: 0