December 4, 2008 at 5:29 am
I want to create a linked server in one sql server to another using the sp_addlinkedserver procedure. When i access the remote server I would like it to logon as me (i.e. using my windows account). How do I do this?
December 4, 2008 at 8:44 am
Do you mean YOU as "currently"? or always use your windows authentication even if the linked server is executed by other SP's or users?
For 2000, Books Online
http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx
Books Online 2005/2008
http://msdn.microsoft.com/en-us/library/ms188477.aspx
Linked server with "Be made using the login's current security context". This is for 2005/2008 though
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'TestServer', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TestServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TestServer', @locallogin = NULL , @useself = N'True'
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply