-- 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.
-- Does not deal with Report login which is removed fro PME 9.0 and needs to be added manually if required. 

-- If running the script manually replace <SERVER_NAME> (one instance) with the host name of the target Primary/Standalone server name

DECLARE @HasSysadminRole BIT
SELECT @HasSysadminRole =  IS_SRVROLEMEMBER('sysadmin', (SELECT SYSTEM_USER))
IF @HasSysadminRole <> 1
	BEGIN
		RAISERROR ('Insufficient privileges to run, CM Tool requires sysadmin privileges to run some SQL scripts. ', 16, 1);
	END
ELSE
	BEGIN
		DECLARE @distributed BIT
		DECLARE @ExpressionToSearch VARCHAR(200)
		DECLARE  @ExpressionToFind VARCHAR(200)
		SET @ExpressionToSearch = (SELECT @@SERVERNAME) -- sql server name 
		SET @ExpressionToFind = (SELECT [Name] FROM [ION_Network].[dbo].[Machine]) -- primary machine name before migration
 
		IF @ExpressionToSearch LIKE '%' + @ExpressionToFind + '%'
			SET @distributed = 0 
		ELSE
			SET @distributed = 1


		USE [ApplicationModules]

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

			--Remove the ION user
			IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'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 N'ION'
			END
		
			-- resynch Login

			
				
					IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'AMUser') 
					BEGIN
					IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N'AMUser')
						BEGIN
							DROP USER  AMUser
						END
						CREATE USER AMUser FROM Login AMUser	
						EXEC sp_addrolemember 'AMApplicationRole', 'AMUser';
					END
			
			

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

			--Remove the ION user
			IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'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 N'ION'
			END


		

				IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'ionedsd') 
					BEGIN
						IF EXISTS (SELECT name FROM sys.database_principals WHERE name = N'ionedsd')
						BEGIN
							DROP USER  ionedsd
						END
						CREATE USER ionedsd FROM Login ionedsd	
						EXEC sp_addrolemember 'NOM_DSD_Reader', 'ionedsd';
					END
			


			USE [ION_SystemLog]

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

			--Remove the ION user
			IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'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 N'ION'
			END

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

			--Remove the ION user
			IF EXISTS (SELECT * FROM dbo.sysusers WHERE name = N'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 N'ION'
			END

		

			IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'ionedsd') 
					BEGIN
						 IF   EXISTS (SELECT name FROM sys.database_principals WHERE name = N'ionedsd')
						 BEGIN
							DROP USER  ionedsd
						 END
						CREATE USER ionedsd FROM Login ionedsd	
						EXEC sp_addrolemember 'ION_DSD_Reader', 'ionedsd';
					END


			USE [ApplicationModules]
			-- Delete all windows users
			DECLARE @sql nvarchar(max)
			SET @sql = ''
			SELECT @sql = @sql+
			'
			execute sp_revokedbaccess ' + '['+name+']'+'
			'
			FROM sys.database_principals dp
			WHERE TYPE = 'U' AND dp.principal_id > 4
			ORDER BY name
			EXECUTE (@sql)	


			USE [ION_Network]
			-- Delete all windows users
			SET @sql = ''
			SELECT @sql = @sql+
			'
			execute sp_revokedbaccess ' + '['+name+']'+'
			'
			FROM sys.database_principals dp
			WHERE TYPE = 'U' AND dp.principal_id > 4
			ORDER BY name
			EXECUTE (@sql)

			USE [ION_Data]
			-- Delete all windows users
			SET @sql = ''

			SELECT @sql = @sql+
			'
			execute sp_revokedbaccess ' + '['+name+']'+'
			'
			FROM sys.database_principals dp
			WHERE TYPE = 'U' AND dp.principal_id > 4
			ORDER BY
					name
			EXECUTE (@sql)

			USE [ION_SystemLog]
			-- Delete all windows users
			SET @sql = ''
			SELECT @sql = @sql+
			'
			execute sp_revokedbaccess ' + '['+name+']'+'
			'
			FROM sys.database_principals dp
			WHERE TYPE = 'U' AND dp.principal_id > 4
			ORDER BY name
			EXECUTE (@sql)
					
			IF  @distributed <> 1 -- Standalone case
				BEGIN
					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

					

					-- Recreate IONMaintenance login and DB users
					USE [master]
					IF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = @nt_account) 
					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'
				END
	END