August 25, 2016 at 3:15 pm
Hello,
Recently we moved our databases from a SQL 2012 to SQL 2014. The instances were created with the same windows logon. I moved the SQL logins successfully. I did not move to windows login. The databases now have a mismatch SID with the SQL 2014 Windows logon. What can I do to fix this problem.
Thank You,
Joe
August 26, 2016 at 2:01 pm
A login is by default created with a random SID. You can force a SID at creation time, so if you can take the downtime (which it sounds like the 2014 instance is "down" anyway), you should drop the SQL logins, and re-create them specifying the SID for each one. Go to the SQL 2012 instance, and run this (may require tweaking):
select 'create login [' + name + '] with password = ', password_hash, ' hashed, sid = ', sid, ', default_database = [' + default_database_name + ']'
from sys.sql_logins
You should be able to identify any logins you need to transfer. Obviously SA can't be transferred. Once the SID's of the logins match the SIDs of the database users, you will be all set.
EDIT: Fix code tag
August 26, 2016 at 2:09 pm
Thanks for the information.
Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply