We have two servers:
LYNDB01 & LYNDB02
On LYNDB01 we have a database called db_ExampleDatabase
We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the login's current security context" option for authentication:
EXEC master.dbo.sp_addlinkedserver @server = N'LYNDB01', @srvproduct=N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LYNDB01',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
We have a windows user on LYNDB02 for DOMAIN\USER1 - this windows user does not have a login on LYNDB01 at all but is a domain admin and thus is a member of BUILTIN\Administrators on both LYNDB01 & LYNDB02
I have created a view in the LYNDB01.db_ExampleDatabase database to confirm what user is running the transaction:
CREATE VIEW [dbo].[vw_CurrentUser] AS SELECT SYSTEM_USER AS CurrentUser
For testing we gave DOMAIN\USER1 RDP access to LYNDB01 & LYNDB02.
If we RDP to LYNDB01 as DOMAIN\USER1 and open up SSMS with windows authentication - we get a logon error as DOMAIN\USER1 does not have a login on LYNDB01, so that's fine.
If we RDP to LYNDB02 as DOMAIN\USER1 and open up SSMS with windows authentication it connects OK (Which is fine as DOMAIN\USER1 has a login on LYNDB02). Here is our problem, we can access LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser via the Linked Server!
SELECT * FROM LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser
How is this possible when LYNDB01 does not have a login on LYNDB01 (Which is confirmed by the error we get when connecting locally!)
And yes, the remote query confirms that DOMAIN\USER1 is the current user??
Edit: Theroy 1
DOMAIN\USER1 is a domain admin and is therefore a local admin on both LYNDB01 & LYNDB02 - could this be causing it? if so, why would i get access denied on LYNDB01 as USER1 has no logon but work via a linked server?
OK, if i delete BUILTIN\Administrators logon from LYNDB01 this problem goes away and the query from LYNDB02 fails - why is BUILTIN\Administrators letting USER1 authenticate remotely via a linked server but not locally & what can i do about it?