moving user id and password from one server to another server

Hi,
I am going to migrate a db from server A to server B. The login information
of the application are using SQL server authenication. From SQL help that
user id and password can be export and move from server A to server B as
following:
-- Server A
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
--Results
0x2131214A212B57304F5A552A3D513453
(1 row(s) affected)
-- Server B
EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
@.encryptopt = 'skip_encryption'
However, i have tried but the result is negative. What else i have missed so
that the above solution does not work? Or what should i do in order to move
the user id and password from server A to server B without changing the user
password? Thanks you very much!
Martin
You mention that this approach fails. My guess is that you have created the
login, but accessing the database fails because the SIDs don't match. Have a
look at this article: http://support.microsoft.com/kb/246133/ (SQL 2005:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx). The output is
login scripts that create logins with the original SID and password. You'll
have to drop the login first, or alternatively you can use
sp_change_users_login if as I suspect the login and user exist on the
destination server but just need mapping together.
HTH,
Paul Ibison
|||Hi
"Atenza" wrote:

> Hi,
> I am going to migrate a db from server A to server B. The login information
> of the application are using SQL server authenication. From SQL help that
> user id and password can be export and move from server A to server B as
> following:
> -- Server A
> SELECT CONVERT(VARBINARY(32), password)
> FROM syslogins
> WHERE name = 'Margaret'
> --Results
> ----
> 0x2131214A212B57304F5A552A3D513453
> (1 row(s) affected)
> -- Server B
> EXEC sp_addlogin 'Margaret', 0x2131214A212B57304F5A552A3D513453,
> @.encryptopt = 'skip_encryption'
> However, i have tried but the result is negative. What else i have missed so
> that the above solution does not work? Or what should i do in order to move
> the user id and password from server A to server B without changing the user
> password? Thanks you very much!
> Martin
>
When moving logins you will need to match sids with the existing system
otherwise the restored database will have orphaned users
http://support.microsoft.com/kb/246133/ gives a procedure that will produce
the script you need to transfer the logins.
John
|||Thx all, i have tried the solution in those link, it works!!!
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6D4D259D-E7AC-4910-9085-9D59D3DE66ED@.microsoft.com...
> Hi
> "Atenza" wrote:
> When moving logins you will need to match sids with the existing system
> otherwise the restored database will have orphaned users
> http://support.microsoft.com/kb/246133/ gives a procedure that will
> produce
> the script you need to transfer the logins.
> John