After my team installed SQL Server 2008 R2 on our project server for testing I experienced annoying problem: I can’t login to it until my domain account was explicitly added to SQL Server. And that’s despite the fact I was member of local Administrators group.
It was very strange, so I decided to investigate. To check that domain is not related to the issue I created local admin account and logged to system from that account. But the result was the same: error 18456. Error message is intentionally uninformative to prevent disclosing of sensitive information. So I took a look at the logs where I found additional ‘state 11’ piece of info. As google told me helpfully state 11 means ‘Valid login but server access failure’ (you can see other states here or here).
Turned out the reason was extremely simple: User Account Control. When I run SQL Server Management Studio without elevated rights UAC effectively ‘removed’ Administrators membership from my account and thus SQL Server couldn’t grant me access, because it is only this membership was my ticket to pass in.
So, the moral of this story: either always run SQL Server Management Studio with elevated rights or turn off the UAC 🙂