Fols Scripts

-- 4 valeurs a modifier
-- 3 fois le code hotel
-- 1 fois le mot de passe du compte ExxxxUsernameFolsSqlGues

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

SET @UsernameFolsSqlGuest = N'EXXXXSqlUserAccountGuest'; -- mettre le nom de base correspondant à l'hôtel
SET @p_Sql_Guest_IN = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'-- Mot de passe du compte ExxxxSqlUserAccountGuest DE L'AD (on le 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''FolsUsers'' AND TYPE_DESC = N''DATABASE_ROLE'')
CREATE ROLE FolsUsers;

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