SQL SERVER 2005 - CREER UN ROLE AUTOMATIQUEMENT (MODE LECTEUR OU UTILISATEUR)

USE [MaBase]
GO

DECLARE @RoleName		AS VARCHAR(100)
DECLARE @SQL			AS VARCHAR(MAX)
DECLARE @TypeRole		AS INT
DECLARE @SQLCMD			AS VARCHAR(200)	

SET @RoleName = 'LECTEUR'
SET @TypeRole = 0 -- 0 = UNIQUEMENT dataReader/executeFN | 1 = dataReader/dataWriter/executeSP/executeFN | 

------------------------------------------------------------------------------------------
---- CREATION DU ROLE
------------------------------------------------------------------------------------------
IF  NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = @RoleName AND type = 'R')
BEGIN
	SET @SQL = 'CREATE ROLE ['+ @RoleName +'] AUTHORIZATION [dbo] '
	EXECUTE (@SQL)
END

IF(@TypeRole = 1) 
	BEGIN
		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES TABLES ET VUES
		---- AJOUTE LES DROITS DE MODIFICATION SUR LES TABLES ET VUES
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE UTILISATEUR SUR LES TABLES ET VUES : DROIT SUR LE SELECT/UPDATE/INSERT/DELETE ET EXECUTE DES SP ET FN' 
		DECLARE CURSORTABLESANDVUES CURSOR FOR SELECT '['+ TABLE_SCHEMA +'].['+ TABLE_NAME +']' AS TABLE_COMPLETE_NAME FROM INFORMATION_SCHEMA.TABLES
		OPEN CURSORTABLESANDVUES
		FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT SELECT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT UPDATE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT DELETE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				SET @SQL = 'GRANT INSERT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
			END
		CLOSE CURSORTABLESANDVUES
		DEALLOCATE CURSORTABLESANDVUES

		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES FONCTIONS ET PROCEDURES STOCKEES
		---- AJOUTE LES DROITS EXECUTE DES SP ET FONCTIONS
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE UTILISATEUR SP ET FUNCTION : DROIT SUR LE EXECUTE' 
		DECLARE CURSORSP CURSOR FOR SELECT '['+ sys.schemas.name +'].['+ sys.objects.name +']' AS SPFN_COMPLETE_NAME FROM sys.objects INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id WHERE sys.objects.type = 'P' OR sys.objects.type = 'FN' ORDER BY sys.objects.type, sys.objects.name
		OPEN CURSORSP
		FETCH NEXT FROM CURSORSP INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT EXECUTE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)

				FETCH NEXT FROM CURSORSP INTO @SQLCMD
			END
		CLOSE CURSORSP
		DEALLOCATE CURSORSP
		PRINT '--------------------------------------------------------------------------------------------------' 

	END
ELSE 
	BEGIN 
		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES TABLES ET VUES
		---- AJOUTE LES DROITS DE SELECTION SUR LES TABLES ET VUES
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE LECTEUR SUR LES TABLES ET VUES : DROIT SUR LE SELECT' 
		DECLARE CURSORTABLESANDVUES CURSOR FOR SELECT '['+ TABLE_SCHEMA +'].['+ TABLE_NAME +']' AS TABLE_COMPLETE_NAME FROM INFORMATION_SCHEMA.TABLES
		OPEN CURSORTABLESANDVUES
		FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT SELECT ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)
				FETCH NEXT FROM CURSORTABLESANDVUES INTO @SQLCMD
			END
		CLOSE CURSORTABLESANDVUES
		DEALLOCATE CURSORTABLESANDVUES

		------------------------------------------------------------------------------------------
		---- BOUCLE SUR LES FONCTIONS
		---- AJOUTE LES DROITS EXECUTE DES FONCTIONS
		------------------------------------------------------------------------------------------
		PRINT '--------------------------------------------------------------------------------------------------' 
		PRINT '-- MISE EN PLACE DU ROLE LECTEUR FUNCTION : DROIT SUR LE EXECUTE' 
		DECLARE CURSORSP CURSOR FOR SELECT '['+ sys.schemas.name +'].['+ sys.objects.name +']' AS SPFN_COMPLETE_NAME FROM sys.objects INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id WHERE sys.objects.type = 'FN' ORDER BY sys.objects.type, sys.objects.name
		OPEN CURSORSP
		FETCH NEXT FROM CURSORSP INTO @SQLCMD
		WHILE (@@fetch_status >= 0) 
			BEGIN 
				SET @SQL = 'GRANT EXECUTE ON '+ @SQLCMD +' TO ['+ @RoleName +']'
				PRINT (@SQL)
				EXECUTE (@SQL)

				FETCH NEXT FROM CURSORSP INTO @SQLCMD
			END
		CLOSE CURSORSP
		DEALLOCATE CURSORSP
		PRINT '--------------------------------------------------------------------------------------------------' 

	END