MOD2000: Permissions Required to Upsize a Microsoft Access Database to SQL Server (244309)

SYMPTOMS

If you want to upsize a Microsoft Access database to a new Microsoft SQL Server database, you must have Microsoft SQL Server CREATE DATABASE permissions. If you want to upsize a Microsoft Access database to an existing Microsoft SQL Server database, you must be either the database owner (dbo) or have CREATE TABLE permissions. Otherwise, you cannot upsize the Microsoft Access database.

CAUSE

If you want to create a new database on SQL Server by using the Upsizing wizard, you must have CREATE DATABASE permissions. These permissions are granted through the sysadmins fixed server role. For example, the sa login (who is a member of sysadmins by default) may create new databases when running the upsizing tool.

To upsize a Jet database to an existing SQL Server database, you must have CREATE TABLE permissions for that database. The dbo database user account is automatically granted these permissions in every SQL Server database. CREATE TABLE permissions may also be assigned directly to a user account.

RESOLUTION

In order for you to upsize a Microsoft Access database to the Microsoft SQL Server, you must be:

a member of the server computer's local Administrators group.

-or-
granted either CREATE DATABASE permissions (for a new database) or CREATE TABLE permissions (for an existing database) by the Microsoft SQL Server administrator.

-or-
the dbo.

MORE INFORMATION

Steps to Reproduce Behavior

Log on to a computer. Be sure to log on as someone not part of the local modAppOwners or Administrators groups of the Microsoft SQL Server computer.
Start Microsoft Access.
Open any Access database (.mdb).
On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
Create a new database.
Run through the wizard. Note that the new database is not created, and that the upsizing is not successful.