SQL Server Agent logs in as guest on a linked server

I have a job called @test_credentials which runs the following query

select * from openquery(SERVER2, 
'select USER_NAME(),* from openquery(SERVER1,''SELECT USER_NAME() '')')

and outputs those results to a file.

Job '@test_credentials' : Step 1, 'test_credentials' : Began Executing 2015-08-31 17:53:45

guest LinkedServerUser

(1 rows(s) affected)

• The linked server definitions for both SERVER1 and SERVER2 are using

the linkedserveruser to login to one another (security context option).

• Both linked server definitions are defined with the same options

• The linkedserveruser user account exists on both servers and is not disabled in anyway.

• The service account running the AGENT is identical for both SERVER1

and SERVER2.

So my question is this:

Why is SQL Server Agent logging in as guest on SERVER2 when executing this job?

Personally I would try using ORIGINAL_LOGIN() instead of USER_NAME(). USER_NAME() returns the database principal or user associated with your login in the database you are connecting to. In this case you are probably connecting to master in both cases. If LinkedServerUser doesn't have a specific login in master on SERVER2 then you will see GUEST.

If on the other hand you use ORIGINAL_LOGIN() you will get the server principal that connected, which will probably be LinkedServerUser in both cases.

StackExchange Database Administrators Q#112745, answer score: 2