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!
MartinMartin
See this link
http://www.dbazine.com/sql/sql-articles/sharma3
regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:%23yhExk4pHHA.4196@.TK2MSFTNGP06.phx.gbl...
> 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:
>> 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