RE:un SP para asignar permisos a tablas
Este te puede servir
-- --------------------------------------------------------------------
-- Da permisologia al rol DB_BIT
-- Autor Fecha Comentarios
-- ------------------------ -------------------------------------------
-- Yamil Bracho 11/OCT/2005 Version Inicial
-- Yamil Bracho 09/Nov/2005 Permisologia para stored proc
-- --------------------------------------------------------------------
DECLARE @table_name VARCHAR(255),
@sql NVARCHAR(255),
@usuario NVARCHAR(255),
@spName NVARCHAR(255),
@cont INT
SET @usuario = 'DB_BIT'
-- --------------------------------------------------------------------
-- Permisologia para las tablas
-- --------------------------------------------------------------------
DECLARE tabla_cursor CURSOR FOR
SELECT name FROM sysObjects WHERE xtype = 'U'
OPEN tabla_cursor
FETCH NEXT FROM tabla_cursor INTO @table_name
SET @cont = 1
WHILE @@FETCH_STATUS = 0
BEGIN
-- PRINT 'Table=' + @table_name
SET @sql = 'GRANT SELECT, INSERT, DELETE, UPDATE ON ' + @table_name + ' TO ' + @usuario
EXEC sp_executesql @sql
SET @cont = @cont + 1
FETCH NEXT FROM tabla_cursor INTO @table_name
END
CLOSE tabla_cursor
DEALLOCATE tabla_cursor
PRINT CAST( @cont AS VARCHAR) + ' tablas procesadas.'
-- --------------------------------------------------------------------
-- Da permiso de ejecucion al rol
-- --------------------------------------------------------------------
DECLARE sp_cursor CURSOR FOR
SELECT name FROM sysObjects WHERE xtype = 'P'
OPEN sp_cursor
FETCH NEXT FROM sp_cursor INTO @spName
SET @cont = 1
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEFT( @spName, 2) = 'p_'
BEGIN
--PRINT 'Stored proc=' + @spName
SET @sql = 'GRANT EXECUTE ON ' + @spName + ' TO ' + @usuario
EXEC sp_executesql @sql
SET @cont = @cont + 1
END
FETCH NEXT FROM sp_cursor INTO @spName
END
CLOSE sp_cursor
DEALLOCATE sp_cursor
PRINT CAST( @cont AS VARCHAR) + ' stored proc procesados.'