/****** Object: StoredProcedure [dbo].[sp_GrantEmployee] Script Date: 03/26/2012 19:34:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Juan Soto IT Impact Inc -- Create date: 3-1-11 -- Description: Grant security rights to employees -- ============================================= CREATE PROCEDURE [dbo].[sp_GrantEmployee] 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, UpdateEmployee, DeleteEmployee, InsertEmployee FROM tblTablePermissions DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname,@UpdateEmployee bit, @DeleteEmployee bit, @InsertEmployee bit, @COLUMN_NAMES varchar(MAX), @sql varchar(MAX) SELECT DISTINCT @Table_Catalog = TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES OPEN myCursor99 FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA,@TABLE_NAME, @UpdateEmployee,@DeleteEmployee, @InsertEmployee WHILE @@FETCH_STATUS = 0 BEGIN Print @Table_Name --Revoke all permissions SELECT @SQL = 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Employee' EXEC(@SQL) SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' +@TABLE_SCHEMA + '.' + @TABLE_NAME + ' TO Employee' --SELECT SQL = @sql EXEC(@SQL) IF @InsertEmployee = 1 BEGIN SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Employee' EXEC(@SQL) END IF @DeleteEmployee = 1 BEGIN SELECT @SQL = 'GRANT Delete ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Employee' EXEC(@SQL) END IF @UpdateEmployee = 1 BEGIN SELECT @SQL = 'GRANT Update ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Employee' EXEC(@SQL) END FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME, @UpdateEmployee,@DeleteEmployee, @InsertEmployee END CLOSE myCursor99 DEALLOCATE myCursor99 END