0

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

2 Answers 2

0

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
0

I had the similar issue. The following script helped me, based on this and this blog posts.

USE [master]
GO
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD='Pa$$w0rd', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
GRANT VIEW SERVER STATE TO ##MS_SSISServerCleanupJobLogin##
GO

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

But first you need to run the following command in CMD to start SQL Server instance.

net start MSSQLSERVER /T902

-or-

net start MSSQL$instancename /T902

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.