SQL 2012 sys databases collation different to instance collation

Question

SQL 2012 sys databases collation different to instance collation

AlfredEnewman

Old Hand

Points: 303
More actions
August 1, 2017 at 11:53 pm

#327835

Hi All
I have a problem where my system databases (master, tempdb, model, msdb) are set to SQL_Latin1_General_CP1_CI_AS and the sql server instance is set to Latin1_General_CI_AS. I think an attempt was made to alter the server instance in the past but cant be sure.
The user databases are all working fine, but the problem comes when trying to view Login properties at the server level. If I right click > view properties of any login I gen an error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. (.Net SqlClient Data Provider)
After searching online it appears I am going to have to run the command Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]/SQLCOLLATION=CollationName in order to get fix this issue. If I do run the above command does anyone know if I detach the user databases before the rebuild and re-attach after the rebuildwill this be ok? Most of the user databases have the SQL_Latin1_General_CP1_CI_AS collation which is what the sql instance collation needs to be and what I would use for the rebuild.
Will I need to script out all the user logins before doing the rebuild (online posts suggest they all get dropped during the rebuild).
Any feedback/comments would be appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 1

I'd be safe, and both backup master and script logins.

The collation issue is probably from some join that causes issues. The same thing can happen with joins in tempdb from your databases.

I'd script logins and users in dbs, just to be safe, and I'd likely detach/attach the user dbs, just to be safe.

Lowell SSC Guru Points: 323518 More actions · Answer 2

there is more script more than just the logins!
Have you created any server roles(i have a server role to allow read any db for auditors and junior dbas)
How about any credentials, like those used for specific processes or the msdb proxy account?
have you set up database mail that has an SMTP username/password? there are credentials created for those.
linked servers?: make sure you script those out as well.

service master keys and database master keys, by chance?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AlfredEnewman Old Hand Points: 303 More actions · Answer 3

Hi All
Thanks for the contributions and apologies for the (very) late reply!. I was pulled into another project and have just come back to this. Its a very simple setup with no mail/SMTP configured, and no additional roles have added other than the default ones that get setup when installing SQL server. I checked and no credentials or proxies have been configured either, so hopefully this wont be too painful to resolve. Will post an update once I have attempted the rebuild.