SQL cannot take the action auto_fix due to duplicate SID
I ran into an unusual Error in SQL “Msg 15331, Level 11, State 1” “cannot take the action auto_fix due to duplicate SID”. I had restored a database from another server and decided to update the database owner name to one that had a mismatched SID.
Here is how to find it:
SELECT u.name, u.sid, sp.name, sp.sid
FROM sys.sysusers u
LEFT JOIN sys.server_principals sp
ON u.name = sp.name
WHERE u.sid != sp.sid
So at first I tried to fix it myself by updating that SID and found:
Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.
The SID for the owner would not repair until I changed the owner of the database to “sa” or myself. (go to db properties, select files, change owner value.) Once this was updated the autofix statement will repair the user: EXEC sp_change_users_login ‘Auto_Fix’, ‘username’
At that point I could set the db owner back to the desired name.