snippetsqlMinor
How can I restore ##MS_SSISServerCleanupJobLogin##?
Viewed 0 times
canms_ssisservercleanupjobloginrestorehow
Problem
At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to this user being missing from the server.
I still have the appropriate User in the SSIS database, but I need to re-create the login. However, I can't find what settings (passwords etc) to use.
The only thing I could find about restoring this user was here, but it doesn't give enough detail.
6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.
6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##
How can I recreate this login properly?
I still have the appropriate User in the SSIS database, but I need to re-create the login. However, I can't find what settings (passwords etc) to use.
The only thing I could find about restoring this user was here, but it doesn't give enough detail.
6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.
6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##
USE SSISDB
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]How can I recreate this login properly?
Solution
I decided to experiment with creating the user with a random password, and everything worked.
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'blahblahblah', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]Code Snippets
CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'blahblahblah', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE SSISDB
GO
ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]Context
StackExchange Database Administrators Q#72738, answer score: 4
Revisions (0)
No revisions yet.