Sql Server - Fixing Error Error: 18456, Severity: 14, State: 11

Question

I'm trying to login to a SQL Server instance from the server it runs on while remoted in as myself.

I keep getting the standard login failed error and in the error log I'm seeing "Error: 18456, Severity: 14, State: 11."

The login is using windows authentication - here's the weird bit. I can login fine from my own machine using the same authentication, just not when I'm on the machine the database is installed on.

Any ideas?

Thanks, Dave

Answer 1

How does UAC work?

When an administrator logs on, this version of Windows creates two separate access tokens for the user: a standard user access token and an administrator access token. The standard user access token contains the same user-specific information as the administrator access token, but the administrative Windows privileges and SIDs have been removed. The standard user access token is used to start applications...

When you're logged in locally your administrator token is stripped. Since you granted access to your instance to BUILTIN\Administrators, you are locked out of the instance. When authenticating remotely the administrator token is preserved and you gain access. You would gain access if you'd choose to RunAs\Administrator when starting your application (SSMS?).

The solution is to grant yourself access explicitly:

create login [domain\you] from windows;
exec sp_addsrvrolemember 'domain\you','sysadmin';