SQL SERVER 2005 - CHANGER LE SCHEMA PAR DEFAUT DE TOUS LES UTILISATEURS POUR UNE BASE SPECIFIQUE

USE [MaBasedeTravail]
GO

DECLARE @NewSchema	AS VARCHAR(50)
DECLARE @DBName		AS VARCHAR(50)
DECLARE @SQLCMD		AS VARCHAR(2000)	

SET @NewSchema = 'MonNouveauSchema'
SET @DBName = 'MaBasedeTravail'


DECLARE CURSORCHANGESCHEMA CURSOR FOR SELECT 'USE ['+ @DBName +'] ALTER USER ['+ name +'] WITH DEFAULT_SCHEMA=['+ @NewSchema +'] ' AS COMMANDSQL 
									FROM sys.sysusers WHERE issqluser = 1 AND hasdbaccess = 1
OPEN CURSORCHANGESCHEMA
FETCH NEXT FROM CURSORCHANGESCHEMA INTO @SQLCMD
WHILE (@@fetch_status >= 0) 
	BEGIN 
		PRINT 'MODIFICATION : '+ @SQLCMD
		EXECUTE(@SQLCMD)
		FETCH NEXT FROM CURSORCHANGESCHEMA INTO @SQLCMD
	END
CLOSE CURSORCHANGESCHEMA
DEALLOCATE CURSORCHANGESCHEMA