migrate linked servers with passwords

This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but
I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:

> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a differen
t
> server. I can get this to work on the same server with the script below bu
t I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go

migrate linked servers with passwords

This is one that I've seen asked often but without a definitive answer:
Is it possible to migrate linked servers including passwords to a different
server. I can get this to work on the same server with the script below but I
can't get it to work on a different server.
--set up
sp_addlogin 'remotelogin','password'
go
use northwind
go
sp_grantdbaccess 'remotelogin'
go
sp_addrolemember 'db_datareader', 'remotelogin'
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'password'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
--get password and paste below
select password from master.dbo.sysxlogins
where name = 'RemoteLogin' and xstatus = 64
--delete linked server
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
EXEC sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
go
sp_addlinkedserver @.server='TestLinkedServer',
@.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
go
sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
'AnyPassword'
go
select count(1) from TestLinkedServer.Northwind.dbo.Customers
update master.dbo.sysxlogins
set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
where name = 'RemoteLogin' and xstatus = 64
select count(1) from TestLinkedServer.Northwind.dbo.Customers
EXEC sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
go
--drop
exec sp_dropserver 'TestLinkedServer' , 'droplogins'
go
use northwind
go
EXEC sp_revokedbaccess 'remotelogin'
go
EXEC sp_droplogin 'remotelogin'
goHi
It is unlikely to work on other servers as the encryption key will differ
between them. if you know the passwords or used windows authentication it
would not be an issue.
John
"DBA72" wrote:
> This is one that I've seen asked often but without a definitive answer:
> Is it possible to migrate linked servers including passwords to a different
> server. I can get this to work on the same server with the script below but I
> can't get it to work on a different server.
> --set up
> sp_addlogin 'remotelogin','password'
> go
> use northwind
> go
> sp_grantdbaccess 'remotelogin'
> go
> sp_addrolemember 'db_datareader', 'remotelogin'
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'password'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
> --get password and paste below
> select password from master.dbo.sysxlogins
> where name = 'RemoteLogin' and xstatus = 64
> --delete linked server
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
>
> EXEC sp_configure 'allow updates', '1'
> RECONFIGURE WITH OVERRIDE
> go
> sp_addlinkedserver @.server='TestLinkedServer',
> @.srvproduct='',@.provider='SQLOLEDB', @.datasrc='ServerName'
> go
> sp_addlinkedsrvlogin 'TestLinkedServer', 'false', NULL, 'remotelogin',
> 'AnyPassword'
> go
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> update master.dbo.sysxlogins
> set password = 0xDEB389AB6A4C1DAEC599EF26C3392578
> where name = 'RemoteLogin' and xstatus = 64
> select count(1) from TestLinkedServer.Northwind.dbo.Customers
>
> EXEC sp_configure 'allow updates', '0'
> RECONFIGURE WITH OVERRIDE
> go
> --drop
> exec sp_dropserver 'TestLinkedServer' , 'droplogins'
> go
> use northwind
> go
> EXEC sp_revokedbaccess 'remotelogin'
> go
> EXEC sp_droplogin 'remotelogin'
> go

migrate database incl. user and password to other server

hi all,
i need to migrate a database including the user and their passwords to
another server.
create the users manually is not possible because i do not know the
passwords. is there a possibility to get them to another db server?
im talking about sql server 2000 sp3
thanks
mic
Hi
There are to stored procedures provided by MS to transfer logins with their
original SID
Search on internet for 'sp_help_revlogin'
"j.goines" <muchos@.hotmail.com> wrote in message
news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
> hi all,
> i need to migrate a database including the user and their passwords to
> another server.
> create the users manually is not possible because i do not know the
> passwords. is there a possibility to get them to another db server?
> im talking about sql server 2000 sp3
> thanks
> mic
>
|||thanks a lot,
greets
mic
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZirDKNIGHA.984@.tk2msftngp13.phx.gbl...
> Hi
> There are to stored procedures provided by MS to transfer logins with
> their original SID
> Search on internet for 'sp_help_revlogin'
>
> "j.goines" <muchos@.hotmail.com> wrote in message
> news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
>

migrate database incl. user and password to other server

hi all,
i need to migrate a database including the user and their passwords to
another server.
create the users manually is not possible because i do not know the
passwords. is there a possibility to get them to another db server?
i´m talking about sql server 2000 sp3
thanks
micHi
There are to stored procedures provided by MS to transfer logins with their
original SID
Search on internet for 'sp_help_revlogin'
"j.goines" <muchos@.hotmail.com> wrote in message
news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
> hi all,
> i need to migrate a database including the user and their passwords to
> another server.
> create the users manually is not possible because i do not know the
> passwords. is there a possibility to get them to another db server?
> i´m talking about sql server 2000 sp3
> thanks
> mic
>|||thanks a lot,
greets
mic
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZirDKNIGHA.984@.tk2msftngp13.phx.gbl...
> Hi
> There are to stored procedures provided by MS to transfer logins with
> their original SID
> Search on internet for 'sp_help_revlogin'
>
> "j.goines" <muchos@.hotmail.com> wrote in message
> news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
>> hi all,
>> i need to migrate a database including the user and their passwords to
>> another server.
>> create the users manually is not possible because i do not know the
>> passwords. is there a possibility to get them to another db server?
>> i´m talking about sql server 2000 sp3
>> thanks
>> mic
>

migrate database incl. user and password to other server

hi all,
i need to migrate a database including the user and their passwords to
another server.
create the users manually is not possible because i do not know the
passwords. is there a possibility to get them to another db server?
im talking about sql server 2000 sp3
thanks
micHi
There are to stored procedures provided by MS to transfer logins with their
original SID
Search on internet for 'sp_help_revlogin'
"j.goines" <muchos@.hotmail.com> wrote in message
news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
> hi all,
> i need to migrate a database including the user and their passwords to
> another server.
> create the users manually is not possible because i do not know the
> passwords. is there a possibility to get them to another db server?
> im talking about sql server 2000 sp3
> thanks
> mic
>|||thanks a lot,
greets
mic
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZirDKNIGHA.984@.tk2msftngp13.phx.gbl...
> Hi
> There are to stored procedures provided by MS to transfer logins with
> their original SID
> Search on internet for 'sp_help_revlogin'
>
> "j.goines" <muchos@.hotmail.com> wrote in message
> news:e6z4rANIGHA.2948@.TK2MSFTNGP10.phx.gbl...
>