Question :
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
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