I installed SQL Server on a machine the other day, I connected from my desktop, finished my normal server setup routine and life went on. Later, I connected to the machine via Remote Desktop, fired up SSMS and got this error message.
With an accompanying error in the log
Login failed for user ‘MyAccount’. [CLIENT: ]
Error: 18456, Severity: 14, State: 11.
Very strange, I thought to myself. Turns out I had added my account to the local administrator’s group and not created a Login on the SQL Server. (It also appears I missed a step on my server checklist, as I normally remove the BuiltinAdministrators group, but who is keeping score?)
I create the Login, and specified the group, and I was good to go.
CREATE LOGIN [DomainAccount] FROM WINDOWS;
EXEC sp_addsrvrolemember [DomainAccount], ‘sysadmin’;