SQL Server – How to Recreate MS_SSISServerCleanupJobLogin User

sql serversql server 2014ssis

During an install of SQL Server 2014 Service Pack 3 we received the following error:

Error: 15151, Severity: 16, State: 1.

Cannot find the user '##MS_SSISServerCleanupJobUser##', because it does not exist or you do not have permission.

Error: 912, Severity: 21, State: 2.

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 15151, state 1, severity 16.

On the instance there is the SQL login [##MS_SSISServerCleanupJobLogin##]

On the SSISDB database there is a SQL user linked to this login called [##MS_SSISServerCleanupJobLogin##] Created the day after the login.

The SSISDB user should be [##MS_SSISServerCleanupJobUser##] and created at the same time as the login.

The solution appears to be to drop the [##MS_SSISServerCleanupJobLogin##] user and create the correct [##MS_SSISServerCleanupJobUser##] user.

USE [SSISDB]
GO
DROP USER [##MS_SSISServerCleanupJobLogin##]
GO

USE [SSISDB]
GO
CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##]
GO
USE [SSISDB]
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] WITH DEFAULT_SCHEMA=[dbo]
GO

Can anyone see any problems arising from dropping and recreating the SSISDB user?

This is a production instance. All test instances have the login and user configured correctly so the service pack installed successfully.

Many Thanks

Simon

Best Answer

I think the solution will work. Don't forget to grant permissions:

USE [SSISDB]
GO
GRANT EXECUTE ON [internal].[cleanup_server_project_version] TO [##MS_SSISServerCleanupJobUser##]
GO
GRANT EXECUTE ON [internal].[cleanup_server_retention_window] TO [##MS_SSISServerCleanupJobUser##]
GO
Related Question