November 21, 2007 at 2:36 am
hi , if we transfers logins from server A to server B thr scripts . server A having existing logins or not ...
before trying ,i have some confusion pls help me
can any one pls help me
November 21, 2007 at 3:04 am
Perfect. you are not moving the logins. Just creating the same logins using script i.e copying the logins in another server. Same were existed in A server and B server. You can gear up.
Regards,
Naveen:)
November 21, 2007 at 3:21 am
If you create the same logins, which means you are creating different sql server logins but having the same name as for your initial instance, what will happen when you are trying to restore your user db from the initial server to the 2nd is that you will not have the sql logins (security at the instance level) and users (security at the db level) synchronised, in which case you will have to drop all users and then re-create them from the newly created logins. I cannot say this is funny if we're talking about>25 users. This is because the new logins will be created with different sids (read in BOL). This is the reason why moving logins is preferred, which will keep the same sids.
It's easy to do this now, please check http://support.microsoft.com/kb/246133, and use Method 2.
Good luck.
November 22, 2007 at 1:12 am
I normally use this script:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND name NOT LIKE ('##%')
AND isntname = 0
Run it on the source server and then run the output on the destination server. You need to either have the default database on the destination server or change the value for defdb though.
hope this helps
November 22, 2007 at 4:01 pm
What is wrong with sp_change_users_login?
November 22, 2007 at 8:39 pm
Danny Springer (11/22/2007)
What is wrong with sp_change_users_login?
Nothing at all. However, if you can script out the logins and recreate them with identical SIDs, that saves you a tremendous amount of work using sp_change_users_login if you have multiple user databases to move/restore.
K. Brian Kelley
@kbriankelley
December 3, 2007 at 1:28 pm
K. Brian Kelley (11/22/2007)
Danny Springer (11/22/2007)
What is wrong with sp_change_users_login?Nothing at all. However, if you can script out the logins and recreate them with identical SIDs, that saves you a tremendous amount of work using sp_change_users_login if you have multiple user databases to move/restore.
It's also good when you refresh dev environments - instead of having to run sp_change_users_login every time you refresh your dev environment from production, you just create the logins once as per above and every other time you just do a restore. Of course, you may want to change any passwords in dev so they don't match production, but that can also be handled by a variation of the script above.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply