-- Deletes and re-adds the IONMaintenance account in master, ION_Network, ION_Data, ApplicationModules, ION_SystemLog.
-- Fixes the ionedsd user in ION_Data and ION_Network.
-- Fixes AMUser in ApplicationModules.
-- Fixes the ENM user for segment editions
-- Removes the ION user with DBO role membership and assigns it as the DBO.

-- NOTE: Replace <NEW_PME_SERVERNAME> with the name of the PME server. There are 7 instances of this in the script that need to be replaced. Use upper-case letters for the server name.
-- Example: DROP LOGIN [PMESERVER\IONMaintenance]


DECLARE @nt_account nvarchar(200)
DECLARE @create_user nvarchar(200)
DECLARE @drop_login nvarchar(200)
DECLARE @create_login nvarchar(200)
declare @nt_account_drop nvarchar(200)

SET @nt_account = (SELECT ('[<SERVER_NAME>\IONMaintenance]'))
SET @create_user = (SELECT 'CREATE USER [IONMaintenance] FOR LOGIN ' + @nt_account)
SET @drop_login = (SELECT 'DROP LOGIN ' + @nt_account)
SET @create_login = (SELECT 'CREATE LOGIN ' +  @nt_account + ' FROM WINDOWS WITH DEFAULT_DATABASE=[master]')
SET @nt_account_drop = (SELECT (CONVERT( nvarchar(100),  (SELECT SERVERPROPERTY('MachineName')))+ '\IONMaintenance'))


USE [master]
IF EXISTS (SELECT * FROM sys.syslogins WHERE name = @nt_account_drop) 
EXEC SP_EXECUTESQL @drop_login

USE [ION_SystemLog]
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'IONMaintenance')
DROP USER [IONMaintenance]

USE ION_Network
IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'ionedsd')
	BEGIN
		CREATE USER ionedsd FROM LOGIN ionedsd	
	END
EXEC sp_addrolemember 'NOM_DSD_Reader', 'ionedsd';

EXEC sp_change_users_login 'Auto_Fix', 'ENM'


IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'IONMaintenance')
DROP USER [IONMaintenance]
EXEC sp_change_users_login 'Auto_Fix', 'ENM'

USE ApplicationModules
ALTER USER AMUser WITH LOGIN = AMUser
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'IONMaintenance')
DROP USER [IONMaintenance]

USE ION_Data
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'IONMaintenance')
DROP USER [IONMaintenance]

IF NOT EXISTS (SELECT name FROM sys.database_principals WHERE name = 'ionedsd')
BEGIN
	CREATE USER ionedsd FROM LOGIN ionedsd
END
EXEC sp_addrolemember 'ION_DSD_Reader', 'ionedsd';

-- Recreate IONMaintenance login and DB users


USE [master]
EXEC SP_EXECUTESQL @create_login

USE [ApplicationModules]
EXEC SP_EXECUTESQL @create_user
EXEC sp_addrolemember N'db_backupoperator', N'IONMaintenance'
EXEC sp_addrolemember N'db_ddladmin', N'IONMaintenance'
EXEC sp_addrolemember N'Maintenance', N'IONMaintenance'

USE [ION_Data]
EXEC SP_EXECUTESQL @create_user
EXEC sp_addrolemember N'db_backupoperator', N'IONMaintenance'
EXEC sp_addrolemember N'db_ddladmin', N'IONMaintenance'
EXEC sp_addrolemember N'Maintenance', N'IONMaintenance'

USE [ION_Network]
EXEC SP_EXECUTESQL @create_user
EXEC sp_addrolemember N'db_backupoperator', N'IONMaintenance'
EXEC sp_addrolemember N'db_ddladmin', N'IONMaintenance'
EXEC sp_addrolemember N'Maintenance', N'IONMaintenance'

USE [ION_SystemLog]
EXEC SP_EXECUTESQL @create_user
EXEC sp_addrolemember N'db_backupoperator', N'IONMaintenance'
EXEC sp_addrolemember N'db_ddladmin', N'IONMaintenance'
EXEC sp_addrolemember N'Maintenance', N'IONMaintenance'


USE [ApplicationModules]

--Remove the ION schema
IF EXISTS (SELECT * FROM sys.schemas WHERE name = 'ION')
BEGIN
	DROP SCHEMA ION
END

--Remove the ION user
IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = 'ION')
BEGIN
	DROP USER ION
END

--Assign the ION login as DBO
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'ION')
BEGIN
	EXEC sp_changedbowner 'ION'
END
GO

