You may be unable to perform most database operations after you log on to an instance of SQL Server 2005 Express Edition Service Pack 1 on a Windows Vista-based computer

Consider the following scenario. You log on to an instance of Microsoft SQL Server 2005 Express Edition Service Pack 1 (SP1) on a Windows Vista-based computer. The account that you use to log on to the instance is a member of the BUILTIN\Administrators group.

In this scenario, you may be unable to perform most database operations. For example, you cannot create a database in SQL Server. Additionally, one or both of the following error messages may be generated:

Error message 1Error message 2

This issue occurs because of User Account Control (UAC) in Windows Vista. UAC is a new security feature in Windows Vista. UAC forces members of the BUILTIN \Administrators group to use a standard user access token. Therefore, SQL Server 2005 Express Edition does not recognize the account as a member of the BUILTIN\Administrators group. Additionally, SQL Server 2005 Express Edition does not add the account to the SysAdmin fixed server role.

To resolve this issue, use either of the following methods.

Method 1

Upgrade the instance of SQL Server 2005 Express Edition to SQL Server 2005 Express Edition Service Pack 2 (SP2) Community Technology Preview (CTP).

On the Configuration Options page of the SQL Server 2005 installation wizard, you must select the Add user to the SQL Server Administrator role option. After you select this option, the account that you use to install SQL Server 2005 Express Edition SP2 CTP is added to the SysAdmin fixed server role in SQL Server.

Note This method adds only the installation account to the SysAdmin fixed server role in SQL Server. If you want to add other accounts to the SysAdmin fixed server role, you can use the SQL Server 2005 User Provisioning Tool for Vista. To use this tool to add an account to the SysAdmin fixed server role, follow these steps:

1. Start SQL Server Surface Area Configuration.
2. Click Add New Administrator.
3. In the SQL Server User Provisioning on Vista window, type the account in the User to provision box.
   
   Note By default, the account that you used to log on to Windows appears in the User to provision box.
4. Under Available privileges, select Member of SQL Server SysAdmin role on SQLInstanceName, click the > button, and then click OK.

Method 2

Manually add the account to the SysAdmin fixed server role in SQL Server 2005 Express Edition. To do this, follow these steps.

Note To use this method, you must install SQL Server Management Studio Express (SSMSE). For more information, visit the following Microsoft Web site:

1. Click Start, point to All Programs, point to SQL Server 2005, right-click SQL Server Management Studio, and then click Run as administrator. The User Account Control dialog box appears.
   
   Note If you do not log on to the computer as an administrator, you are prompted to provide the administrator account. When you are prompted to provide the administrator account, type the administrator user name and password in the User Account Control dialog box. Then, click OK.
2. Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005 Express Edition.
3. Add the account that you want to use to the SysAdmin fixed server role. To do this, follow these steps:
   1. Click Security.
   2. Right-click Logins, and then click New Login.
   3. In the Login-New dialog box, type the user name of the Windows user account in the Login name box.
   4. Click Server Roles.
   5. Click to select the sysadmin check box, and then click OK.

Note You must follow these steps for each Windows user or group that you want to use as a member of the SysAdmin fixed server role.

This behavior is by design.

The following SQL Server 2005 Express Edition versions are supported in Windows Vista:

• SQL Server 2005 Express Edition SP1
• SQL Server 2005 Express Edition SP2 CTP or later versions of SQL Server 2005 Express Edition
• SQL Server 2005 Express Edition with Advanced Services Service Pack 2 (SP2) CTP or later versions of SQL Server 2005 Express Edition with Advanced Services

For more information about how to obtain the latest service pack for SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base: Be aware that SQL Server 2005 Express can be installed by other Microsoft software and also by third-party software.

By default, the standard user access token is a part of the BUILTIN\Users group. Therefore, you can log on to the instance of SQL Server 2005 Express Edition. Users in the BUILTIN\Users group can log on to the instance of SQL Server 2005 Express Edition. However, these users do not have administrator permissions.