0

I have two databases, on two different servers, with different credentials to access them.

I need to call a stored procedure in the second database from within the context of the first database. However, for security purposes, the Users are not common between the two databases.

When I call:

DECLARE @OtherServer varchar(100) = 'MySecondServer'
DECLARE @OtherDatabase varchar(100) = 'MySecondDatabase'
DECLARE @sql nvarchar(4000) = N'SELECT * FROM ['+@OtherServer +'].['+@OtherDatabase +'].dbo.Customer_Info'
EXEC sp_executesql @sql

How do I specify what User name and Password to pass, if it isn't identical to how I'm connected to the first database?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Daniel Bragg
  • 1,773
  • 1
  • 12
  • 25
  • I am currently picking through http://www.sommarskog.se/grantperm.html in the hopes that it will help my understanding, but it's a *huge* read, and any assistance in pointing me to relevant passages would be helpful. – Daniel Bragg Sep 28 '18 at 18:57
  • Look into "SQL Server Linked Server" feature. – Alex Sep 28 '18 at 21:15
  • He has an interesting section using EXEC and "AT {linkedServerName}" that I believe may be where we go with this: http://www.sommarskog.se/dynamic_sql.html#EXECatlinked – Daniel Bragg Sep 28 '18 at 22:19

1 Answers1

0

Although I hate to just give you a link, the BOL documentation for sp_addlinkedserverlogin (and I assume probably some of the related articles) covers it to a depth I think should answer your question.

To the remote server, the call is just like any other request. It's the linked server definition, and it's associated roles which control the credentials it connects with.

Specifically, look at example "B" which discusses associating a linked server to a specific user.

Xedni
  • 3,662
  • 2
  • 16
  • 27