Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??

VERY confused?!

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

This is expected. LUA strips administrators membership from your token. From Getting Started with User Account Control on Windows Vista:

To help prevent malware silent installation and computer-wide infection, Microsoft developed the UAC feature for Windows Vista. Unlike previous versions of Windows, when an administrator logs on to a Windows Vista computer, the user’s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed or disabled, resulting in a standard user access token. The standard user access token is then used to launch the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well. Contrasting with this process, when a standard user logs on, only a standard user access token is created. This standard user access token is then used to launch the desktop.

Since your local desktop token has the administrator membership removed, it cannot log in locally to SQL Server if you expect to log in through builtin\Administrators. On remote access your token preserves the Administrator membership and you gain access.

The solution is to grant SQL Server login rights through an explicit domain group account and add all users that require access to your SQL instance to this domain group. Do not rely on Administrators membership, is a bad practice.

share|improve this answer
    
Argh, so simple now i think about it - cheers. The reason this came up is that we realised our domain admins pretty much had full access to SQL via linked servers - is this something we should just accept and trust our Domain Admins? Everything ive read about removing the BUILTIN\Administrators group says that this can often be more trouble than it's worth? –  HeavenCore Apr 17 '13 at 9:27
2  
Domain admins have access to everything, you cannot block that. If you don't trust them, don't give them the keys of the kingdom... –  Remus Rusanu Apr 17 '13 at 9:29
    
Agreed – to be clear, the Domain Admin issue was raised by management above me – just wanted to get my facts right before getting back to them – thank you for the information. –  HeavenCore Apr 17 '13 at 9:31
    
As for the trouble of removing builtin\administrator, I'm not sure what you've read. I never had any problem. Remeber that builtin\administrators can always get access if they want, is documented on msdn.microsoft.com/en-us/library/dd207004.aspx –  Remus Rusanu Apr 17 '13 at 9:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.