Special Edition Using Microsoft Office Access 2003

Users who aren't members of the local Administrators group of the machine hosting SQL Server don't have access to the linked databases you created in the preceding sections. When you log on to the front-end .mdb with a non-administrative Windows account you receive an SQL Server "Connection failed" error message (see Figure 19.29, top). Clicking OK opens an inoperative SQL Server Login dialog (see Figure 19.29, bottom). At this point, your only option is to click Cancel to display the original error message, click OK to open the dialog, and then click Cancel again.

Note

If you marked the Save Password and User ID check box when upsizing the tables, you receive a different pair of error messages: "ODBC-call failed" and "Can't open table in datasheet view."

OSQL lets you execute T-SQL statements, which include the EXEC[UTE] statement for stored procedures. Installing SQL Server generates a large number of system stored procedures, which apply to all server databases. You use OSQL to execute several of these system stored procedures, which have an sp_ prefix, to add server logins and database permissions for users or groups. It's more common to add Windows 2000/.NET security groups for database access, but adding individual user accounts is appropriate when you only have a few users.

You use the following system stored procedures to add or revoke logins and permissions for users or groups in the default public server role:

• sp_grantlogin 'DOMAIN\LogonID' to add a login with Windows authentication. LogonID can be the name of a user or group. If you're running MSDE 2000 on your local computer that's not a member of a Windows 2000/.NET domain, omit DOMAIN\. Execute sp_revokelogin with the same argument to remove the login.

• sp_grantdbaccess 'login', 'UserOrGroupName' gives the user or group access to the current database. Ordinarily, you specify LogonID as the value of login. You set the current database with the USE 'databasename' statement. Executing sp_revokedbaccess with the same arguments revokes database access.

• sp_addrolemember 'RoleName', 'login' assigns the user or group to a predefined or custom role in the current database, such as db_reader or db_writer. Revoking database access removes that user from all database role(s).

By default, members of the public server role have no database permissions.

The following example adds with OSQL two logins NWReader1 and NWWriter1 on a remote SQL Server (OAKLEAF-MS10) for the two members of the local Power Users group that have file-level permissions for the linked NWData.mdb back-end. NWReader1 receives read-only permissions (the db_datareader role) and NWWriter1 receives read-write permissions (db_datareader and db_datawriter) to the NWDataSQL database. You can use either a remote or local version of the NWDataSQL database.

If you didn't create the two Power User accounts, see "Establishing Network Share and File Security for the Back End," p. 747.

If you didn't create the NWDataSQL database, see "Upsizing an Application with Linked Tables," p. 746.

Follow these steps and refer to Figure 19.30 to add the logins and permissions for the two users:

1. Log on with an administrative account to the machine running SQL Server, open a command prompt, and type osql -E to start OSQL and display a numbered prompt (1>). Press enter after each instruction.

2. Type EXEC sp_grantlogin 'OAKLEAF\NWReader1' and type GO to execute the instruction and add the login. OSQL confirms the operation or returns an error message.

3. Type EXEC sp_grantlogin 'OAKLEAF\NWWriter1' and type GO.

4. Type USE NWDataSQL and GO to make NWDataSQL the current database. Note that no single quotes surround NWDataSQL, which is the name of a database object.

5. Type EXEC sp_grantdbaccess 'OAKLEAF\NWReader1', 'NWReader1' and GO to grant NWReader1 access to the NWDataSQL database.

6. Type EXEC sp_grantdbaccess 'OAKLEAF\NWWriter1', 'NWWriter1' and GO.

7. Type EXEC sp_addrolemember 'db_datareader', 'NWReader1' and GO to add NWReader1 to the read-only role. Do the same for NWWriter1.

8. Type EXEC sp_addrolemember 'db_datawriter', 'NWWriter1' and GO to enable NWWriter1 in the read-write roles.

9. Type quit to exit OSQL.

10. Log on to the NWClient.mdb front-end workstation with the NWReader1 account and open a table, such as Categories. The tentative append record is present, but you receive an "ODBC - insert on a linked table 'Categories' failed" error message when you attempt to add a new record to the table.

11. Log off and log on with the NWWriter1 account, and verify that you can add a new record to one of the tables and delete the added record.

Tip

If you have a large number of users or groups that need access to several databases, you can write a T-SQL script in Notepad or your favorite text editor, and then run the script with the OSQL -E -q -i Path\Script.sql [-o Path\Result.sql] command. The -q parameter causes OSQL to remain open after execution; -i specifies the T-SQL script file. The optional -o parameter specifies an output file that includes the commands and responses.