Fols Scripts

-- 4 valeurs a modifier
-- 3 Fois le code hotel
-- et le mot de passe du compte SqlUserAccountGuest

DECLARE @UsernameFolsSqlGuest NVARCHAR(50)
DECLARE @p_Sql_Guest_IN NVARCHAR(300)
DECLARE @EXECSQL NVARCHAR(MAX)

SET @UsernameFolsSqlGuest = N'SqlUserAccountGuest';
SET @p_Sql_Guest_IN = 'a8b42ac8-b3b8-4ccd-a737-fdd1f4ab128c'
-- LE MOT DE PASSE DU COMPTE SqlUserAccountGuest DE L'AD et que l'on trouve aussi dans le CONNEXION.STRING

IF EXISTS (SELECT 1 FROM [master].sys.databases WHERE name like 'EXXXX')
BEGIN
IF NOT EXISTS (SELECT 1 FROM master.sys.server_principals where name = @UsernameFolsSqlGuest and type_desc =N'SQL_LOGIN')
BEGIN
SET @EXECSQL =convert(nvarchar(max),N'USE [MASTER];
CREATE LOGIN ' + @UsernameFolsSqlGuest + ' WITH PASSWORD=N''' + @p_Sql_Guest_IN + ''';');
EXEC [master]..sp_executeSQL @EXECSQL;
END
ELSE
BEGIN
SET @EXECSQL =convert(nvarchar(max),N'USE [MASTER];
ALTER LOGIN ' + @UsernameFolsSqlGuest + ' WITH PASSWORD=N''' + @p_Sql_Guest_IN + ''';');
EXEC [master]..sp_executeSQL @EXECSQL;
END

SET @EXECSQL =N'USE [EXXXX];
IF EXISTS(select 1 from sys.database_principals where name=N''' + @UsernameFolsSqlGuest + ''' and type_desc =N''SQL_USER'' and is_fixed_role =0)
BEGIN
ALTER USER ' + @UsernameFolsSqlGuest + ' WITH NAME=' + @UsernameFolsSqlGuest + ', LOGIN=' + @UsernameFolsSqlGuest + ';
END
ELSE
BEGIN
CREATE USER ' + @UsernameFolsSqlGuest + ' FOR LOGIN ' + @UsernameFolsSqlGuest + ' WITH DEFAULT_SCHEMA = dbo;
END'
EXEC [master]..sp_executeSQL @EXECSQL;

SET @EXECSQL =N'USE [EXXXX];
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE NAME = N''FolsGuest'' AND TYPE_DESC = N''DATABASE_ROLE'')
CREATE ROLE FolsGuest;

----
IF EXISTS (SELECT * FROM sys.database_principals WHERE NAME = N''' + @UsernameFolsSqlGuest + ''' AND TYPE_DESC = N''SQL_USER'')
exec sys.sp_addrolemember N''FolsGuest'', N''' + @UsernameFolsSqlGuest +'''; ';
EXEC [master]..sp_executeSQL @EXECSQL;
END