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. Figure 19.29. A user without an SQL Server login receives the following two messages when attempting to open an upsized table or front-end objects, such as queries, that are bound to upsized tables.
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:
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: Figure 19.30. This series of T-SQL commands in the OSQL utility add two logins, database access, and database user permissions for the linked NWDataSQL database.
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. |