How to: Connect to SQL Server from Windows Vista

The Windows Vista operating system provides a new security feature named User Account Control (UAC). User Account Control lets users perform common tasks as non-Administrators. Windows user accounts that are members of the local Administrators group can run most applications as if they using the Standard User account.

Using the Standard User account helps protect the system because all applications that are run by administrators in Windows Vista do not have full administrator permissions. Programs that do require administrator permissions, such as tools to administer the system, are started with administrator permissions after the user provides consent.

SQL Server installs with the BUILTIN\Administrators group as the default system administration group. By default, users on Windows Vista that are members of the Windows Administrators group are not automatically granted permission to connect to SQL Server, and they are not automatically granted SQL Server administrator privileges. On Windows Vista, when a user tries to connect to SQL Server, a message is returned that states that the account does not have rights to log in to SQL Server.

Note

In SQL Server Express, Windows users are granted connect permission because the BUILTIN\Users group is provided as a login.

When a user in the Administrators account runs SQL Server Management Studio, the User Account Control feature strips the membership token for that group and passes only the user account information to SQL Server. A message is returned that states that the account does not have rights to log in to SQL Server. To let members of the Windows Vista Administrators group log in, you must explicitly add the account to the SQL Server logins.

Note

If you use the format username@domain when you log on to Windows Vista, you might see a SQL Server login failure or an access denied message. To resolve this issue, use one of the following formats to log in to Windows Vista: domain\username or username@fully_qualified_domain.

To add a Windows user that has the login "machinename\Administrator" to the sysadmin fixed server role

  1. Log on to the computer using the credentials for the machinename\Administrator account.

  2. Click the Start button, point to All Programs, click Microsoft SQL Server, and then click SQL Server Management Studio.

  3. Connect to an instance of SQL Server.

  4. Click Security, right-click Logins, and then click New Login.

  5. In the Login name box, enter the user name.

  6. In the Select a page pane, click Server Roles, select the sysadmin check box, and then click OK.

To add a Windows user that has the login "domainname \username" to the sysadmin fixed server role

  1. Log on to the computer using the credentials for the domainname\username account.

  2. Click the Start button, point to All Programs, click Microsoft SQL Server, right-click SQL Server Management Studio, and then click Run as administrator.

    Note

    The Run as administrator option elevates the user permissions.

  3. In the User Access Control dialog box, click Continue.

  4. In SQL Server Management Studio, connect to an instance of SQL Server.

  5. Click Security, right-click Logins, and then click New Login.

  6. In the Login name box, enter the user name.

  7. In the Select a page pane, click Server Roles, select the sysadmin check box, and then click OK.