Hay tareas que son repetitivas sobre las cuales siempre hay que pensar "Debe haber una mejor forma que hacer esto que uno por uno". Pues una de esas es asignar permisos a las tablas o vistas de una base de datos.
Para esto me encontré y modifique el siguiente script para aplicar los permisos de todas las tablas (que comiencen con cierto prefijo) y vistas de una base de datos a un rol, aplica para SQL SErver 2005:
-- Declaracion de variables para el cursor
DECLARE @sql nvarchar(255),
@name varchar(250),
@type varchar (250)
/*PERMISOS PARA EL ROL QUE QUEREMOS*/
-- Declaración del cursor
DECLARE cPermisos CURSOR FOR
--Para esto caso se usa el prefijo SEC para las tablas de seguridad y GEN para las generales
SELECT table_name ,table_type FROM INFORMATION_SCHEMA.TABLES
WHERE table_name like 'SEC%' or table_name like 'GEN%'
-- Apertura del cursor
OPEN cPermisos
-- Lectura de la primera fila del cursor
FETCH cPermisos INTO @name, @type
WHILE (@@FETCH_STATUS = 0 )
BEGIN
if (@type = 'BASE TABLE') begin
--Permisos a las tablas para el rol que necesitemos
SET @sql = 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' + @name + ' TO ROL_ASIGNADO'
--Ejecuta la sentencia sql
EXEC sp_executesql @sql
end
if (@type ='VIEW')begin
--Permisos a las vistas al rol que necesitemos
SET @sql = 'GRANT SELECT ON ' + @name + ' TO ROL_ASIGNADO'
--Ejecuta la sentencia sql
EXEC sp_executesql @sql
end
-- Lectura de la siguiente fila del cursor
FETCH cPermisos INTO @name,@type
END
-- Cierre del cursor
CLOSE cPermisos
-- Liberar los recursos
DEALLOCATE cPermisos
En este ejemplo al Rol al que le asignamos los permisos deseados se llama ROL_ASIGNADO, obviamente lo reemplazamos para el rol que necesitamos según cada caso particular.
Para el caso de los store procedures es bastante similar
-- Declaracion de variables para el cursor
DECLARE @sql nvarchar(255),
@name varchar(250)
-- Declaración del cursor para los permisos de los procedimientos almacenados que no sean de reportes
DECLARE cPermisos CURSOR FOR
SELECT name FROM SAPEX.dbo.sysobjects WHERE xtype = 'P'
-- Apertura del cursor
OPEN cPermisos
-- Lectura de la primera fila del cursor
FETCH cPermisos INTO @name
WHILE (@@FETCH_STATUS = 0 )
BEGIN
--Permisos a las tablas para el rol que se nos asigne o hayamos creado
SET @sql = 'GRANT EXECUTE ON ' + @name + ' TO ROL_ASIGNADO'
--Ejecuta la sentencia sql
EXEC sp_executesql @sql
-- Lectura de la siguiente fila del cursor
FETCH cPermisos INTO @name
END
-- Cierre del cursor
CLOSE cPermisos
-- Liberar los recursos
DEALLOCATE cPermisos
Estos dos script, modificados según nuestras necesidades, nos puede hacer la vida más sencilla cuando se trata de asignar permisos a los objetos de la base de datos.