Connecting to SQL Server 2005 on Vista and Longhorn

Some customers have experienced problems connecting to SQL Server 2005 on Vista and Longhorn. Even though they are trying to connect using an account that is an administrator on the box, they get a "Login failed for user" error message with a state of 11.

The reason for this is that Windows Vista includes a new feature, User Account Control (UAC), which helps administrators manage their use of elevated privileges, and Windows Vista users that are members of BUILTINAdministrators are not automatically added to the sysadmin fixed server role when they connect to SQL Server.

SQL Server 2005 SP2 provides an option during the setup process allows you to automatically add the user who is running setup into the SysAdmin Fixed Server Role. This option is turned off by default. To enable it, users can use the Setup UI by checking the checkbox in the Configuration Options page of the Setup UI with the caption 'Add user to the SQL Server Administrator role'. Alternatively, this can be done using the setup command line by specifying the ADDUSERASADMIN=1 option in order to add a login for the user running setup. To not add a login for that user, use ADDUSERASADMIN=0 (the default behavior).

This is discussed in detail at https://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx.

Users can always connect to SQL Server using elevated privileges (run as Administrator), or connect as sa (if Mixed authentication mode was selected during install). Once connected, any user can be granted login privileges or added to the sysadmin fixed server role by executing

EXEC sp_grantlogin 'domainuser'

or

EXEC sp_addsrvrolemember 'domainuser', 'sysadmin'

respectively.

Once this is done, users who have been granted login privileges or added to the sysadmin fixed server role no longer need to run as administrator in order to connect to SQL Server.

Stoyko Kostov, SQL Server Protocols