Sql-server – SQL Server Agent logs in as guest on a linked server

Securitysql-server-agent

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)

  1. The linked server definitions for both SERVER1 and SERVER2 are using
    the linkedserveruser to login to one another (security context option).
  2. Both linked server definitions are defined with the same options
  3. The linkedserveruser user account exists on both servers and is not disabled in anyway.
  4. 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?

Best Answer

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.

Related Question