Cannot login to SQL Server 2008 R2 with Windows authentication

Solution 1:

As Matt has stated the Windows login you are using has not been setup and you will need to add it with a login that is a member of the sysadmin fixed server role.

You do not need to be a member of the sysadmin fixed server role to simply connect to your SQL Server 2008 R2 instance.

BTW, during the R2 install, one is able to specify login(s) that need to be added to the sysadmin fixed server role.

UPDATE - How to enable the adding of groups in SQL Server security

After including groups, you can find them (if they exist) using the Check Names in the Select User or Group pop-up.

Solution 2:

@squillman provided the solution in a separate question.

This is a known issue with SQL Server 2008, and future products. The fix is to run:

CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS
go
sp_addsrvrolemember
      @LogiName='BUILTIN\Administrators',
      @RoleName='sysadmin'

The important point being that cannot add a "group" to a server role directly. You have to add a "login" for that "group". Then you can add that "login":

e.g. Fails:

BUILTIN\Administrators -> sysadmin

Works:

BUILTIN\Administrators -> BUILTIN\Administrators -> sysadmin