/****** Object: StoredProcedure [dbo].[sp_GrantClient] Script Date: 03/26/2012 19:32:34 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Juan Soto, IT Impact Inc. -- Create date: 3/1/11 -- Description: Procedure used to grant client rights to tables in tblPermissions -- ============================================= CREATE PROCEDURE [dbo].[sp_GrantClient] AS BEGIN SET NOCOUNT ON; -- Insert statements for procedure here DECLARE myCursor99 CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME, SelectClient, UpdateClient, DeleteClient, InsertClient FROM tblTablePermissions DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname,@sql varchar(MAX),@SelectClient Bit, @UpdateClient bit, @DeleteClient bit, @InsertClient bit SELECT DISTINCT @Table_Catalog = TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES OPEN myCursor99 FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA,@TABLE_NAME, @SelectClient, @UpdateClient, @DeleteClient, @InsertClient WHILE @@FETCH_STATUS = 0 BEGIN --Revoke all permissions SELECT @SQL = 'REVOKE SELECT, INSERT, UPDATE, DELETE ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) -- Deny All Permissions SELECT @SQL = 'DENY SELECT, INSERT, UPDATE, DELETE ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) Print @Table_Name IF @SelectClient = 1 BEGIN SELECT @SQL = 'GRANT SELECT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) END IF @InsertClient = 1 BEGIN SELECT @SQL = 'GRANT INSERT ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) END IF @DeleteClient = 1 BEGIN SELECT @SQL = 'GRANT Delete ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) END IF @UpdateClient = 1 BEGIN SELECT @SQL = 'GRANT Update ON ' + @TABLE_CATALOG + '.' + @TABLE_SCHEMA+ '.' + @TABLE_NAME + ' TO Client' EXEC(@SQL) END FETCH NEXT FROM myCursor99 INTO @TABLE_SCHEMA, @TABLE_NAME, @SelectClient, @UpdateClient, @DeleteClient, @InsertClient END CLOSE myCursor99 DEALLOCATE myCursor99 END