August 26, 2003 at 11:56 am
I’ve used detach/attach and backup/restore to move the database from one server to another, I always have trouble on transferring the logins, I read the knowledge base articles on transferring the logins, still did not fully understand it.
According to the article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q246133
1.) When execute the result of SP_help_revlogin on the destination server, do the accounts that were generated from source server will overwrite the accounts on the destination server, do they have the same SID for each account.
2.) Sp_change_users_login stored procedure is to correct the orphaned users one-by-one (this will only address users orphaned from standard SQL logins), what about integrated logins?
3.) Do the original logins SID will be mapped to the destination server if Master DB was restored. By the way, I’ve never restore the master DB successfully.
Could somebody tell me the procedure transferring the logins (same SID) from one server to another, include standard SQL logins and integrated logins? Thanks!
August 27, 2003 at 6:54 am
This is how I handle this situation:
1. run sp_dropuser for the database
2. for SQL logins run sp_addlogin. for Windows logins run sp_grantlogin
3. run sp_grantdbaccess for the login(s)
4. run sp_addrolemember for appropriate database role(s).
I hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply