Tuesday, August 16, 2011

Cannot logon to newly installed SQL Server

Problem: After a fresh install of SQL Server 2008 R2 on a Windows 7 computer, where Windows user group BUILTIN\Administrators were given administrative rights, I was not able to log on to the database from Microsoft SQL Server Management Studio while logged on to Windows as a user who was a member of the Administrators group and thus should have been granted access. The error received was: Login failed for user 'COMPUTERNAME\user'. (Microsoft SQL Server, Error: 18456).

Solution: Turn off User Account Control, or if you prefer to leave it on, make sure you start SQL Server Management Studio as an administrator (right-click the icon and select Run as administrator).