This article applies only to a Microsoft Access project (.adp).
With a Full Installation of SQL ServerGrant permission for the specified account on the database that you are trying to connect to. Access projects can connect to SQL Server through a specific SQL Server user or by using a network domain account. With full installations of SQL Server, you can use Enterprise Manager to create user accounts and grant permissions.
With SQL Server 2000 Desktop EngineA SQL Server 2000 Desktop Engine installation does not include Enterprise Manager. Addionally, by default, SQL Server 2000 Desktop Engine is configured to allow only members of the local Windows NT or Windows 2000 administrators group or the SQL Server Administrator account, "sa," to access the database.
To grant permissions to the database in SQL Server 2000 Desktop Engine, you can use the following options:
- You can use Integrated Security to add the user who is running the Access project to the Windows NT or Windows 2000 local administrators group. However, this also gives the user full access to the computer.
- You can enable the "sa" account with a password and allow the user to log on as a SQL Server Administrator. The user will have full access to the SQL Server, but not full access to the Windows NT-based computer or the Windows 2000-based computer.
- You can install SQL Server Enterprise Manager from the SQL Server 7.0 or SQL Server 2000 compact disc. This gives you the management tools to add network domain users to the database and to provide security at the object level.
- You can install Microsoft Office XP Developer. Microsoft Office XP Developer includes the SQL Server client tools, including Enterprise Manager.
- You can execute SQL Server stored procedures to grant access to the database.
To allow access to the database, you must grant the user permissions. For integrated security, you must make the user a member of the local admins group on the Windows 2000 server. For Windows 98, you must use the SQL Server administrative account, "sa."
There are additional options that you can use to provide more restricted security. These involve installing Enterprise Manager or executing SQL Server stored procedures to create logons and database access rights.
Steps to Reproduce the Behavior
- Use Enterprise Manager to create two new databases on SQL Server. Name them Test1 and
- Create a new logon. Set its default database as Test1.
- Add this new logon as a database user on Test1.
- Create a new Access project (existing data).
- In step 1 of the Data Link Properties dialog box, select your server. In step 2, click Use a specific user name and password. Then, enter the user name and password of your new user.
- Click Test Connection to verify that the connection works.
- In step 3 of the Data Link Properties dialog box, select the Test1 database. Then, click Test Connection. Note that the connection works.
- In step 3 of the Data Link Properties dialog box, select the Test2 database. Note that you receive the second error message that is mentioned in the "Symptoms" section of this article.
- Click OK to the error message that you receive in step 8. Then, click OK in the Data Link Properties dialog box. Note that you receive the first error message that is mentioned in the "Symptoms" section of this article.
Article ID: 297373 - Last Review: Nov 12, 2012 - Revision: 1