/****** Object: StoredProcedure [dbo].[sp_GrantAdmin] Script Date: 03/26/2012 19:26:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Juan Soto -- Create date: 3/1/11 -- Description: Will grant admin rights to all tables in tblTablePermissions -- ============================================= CREATE PROCEDURE [dbo].[sp_GrantAdmin] -- Add the parameters for the stored procedure here AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE myCursor99 CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM dbo.tblTablePermissions DECLARE @Table_Catalog sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX) SELECT DISTINCT @Table_Catalog = TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES --PRINT 'Catalog: ' + @Table_Catalog SELECT @COLUMN_NAMES = '' OPEN myCursor99 FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA,@TABLE_NAME WHILE @@FETCH_STATUS = 0 BEGIN Print @Table_Name SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME + ' TO Admin' --SELECT SQL = @sql EXEC(@SQL) SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Admin' EXEC(@SQL) SELECT @SQL = 'GRANT Delete ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Admin' EXEC(@SQL) SELECT @SQL = 'GRANT Update ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Admin' EXEC(@SQL) FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME END CLOSE myCursor99 DEALLOCATE myCursor99 END