detach, transfer, orphans | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

detach, transfer, orphans

I’ve used the sp_’s from this article:
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133 And still got orphaned users. Here’s what I did: 1. Script users on master server with sp_’s in KB article.
2. Detach
3. FTP
4. Attach
5. Load users from sp_’s output. user databases still had no link to sql server logins
(not using windows auth by the way, only sql server logins) I had to use sp_change_users_login but I should not have had to. What went wrong ?

In order to fix orphaned users you must use SP_CHANGE_USERS_LOGIN statement, as there is no other go. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
So there is nothing I could have done to prevent orphan users when I moved the databases? I thought that was the point of using the "transfer logins" stored proc. MS created.

Run
EXEC sp_validatelogins
to check the orphaned logins whenever any login transfer occurs from one server to another. The orphaned users issue occurs as each user name is stored in the syslogins table with a unique "SID" (for SQL 2000 – SUIDs for SQL 6.x or earlier) on each server. When a database is copied to a new server the SIDs for the usernames no longer match. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>