Securing Projects with the MSDE 2000 Login/User Tool Adding password protection and converting your upsized project to an encoded (.ade) file doesn't provide security for your back-end SQL Server database. You must provide groups or individual users logins to the server and permissions to read and update specific tables. Issuing a long series of OSQL statements at the command prompt is time-consuming and prone to error. Writing an OSQL script to add logins and permissions is an alternative, but isn't an easy task.  For more information on creating .ade files and using the OSQL utility, see "Securing Your Project as an .ade File," p. 848 and see "Adding User Logins with the OSQL Utility," p. 776.
The MSDE User Manager utility (UserMan.exe) is a Visual Basic 6.0 program that uses SQL Server Data Management Objects (SQLDMO) to manage server logins and database permissions for MSDE 2000. (The MSDE 2000 setup program installs SQLDMO.dll.) UserMan.exe supports Windows authentication only, because Windows authentication is more secure than SQL Server security. UserMan.exe lets you assign groups or users to predefined SQL Server roles such as db_datareader and db_datawriter grant specific object permissions, or both. Users or groups must have Windows accounts before you can add SQL Server logins for them. Like the Crosstab Upsizer, the MSDE User Manager utility has a detailed help file, so the following steps demonstrate basic functionality: Run Setup.exe from your \Program Files\Seua11\UserMan folder. By default, the program installs in your \Program Files\MSDE User Manager folder and adds an Programs, MSDE User Manager shortcut. If your or your network administrator haven't established Windows accounts for your project's users, do this now. If your computer isn't on a network, add a few local test users to the built-in Power Users group. Choose Programs, MSDE User Manager to open the utility's single form, which displays in the upper left list all SQL Server instances that are accessible to your computer. For this example, the (local) server is OAKLEAF-XP1 in the OAKLEAF domain. (Click the Help tab for detailed instructions.) Select a server instance in the list and click Connect to open a connection and fill the ServerName Logins and ServerName Databases lists. Click an item in the ServerName Databases list to display its user accounts in the DatabaseName Users list. For this example, the UpsizeSQL database has only the sysadmin user (dbo). Click dbo to display the sysadmin roles in the UserName Roles list (see Figure 22.37). All users are a member of the public role. Figure 22.37. The MSDE User Manager utility has lists of server instances, logins for the selected instance, databases, database users, and user roles.  Type the group or user name preceded by the domain, local computer name, or BUILTIN for Windows XP/2000 built-in groups in the text box and click Add Login. If the login name is valid and you've marked the Add User to DatabaseName check box, the program adds the login to the server and creates a user account in the specified database. If you type an existing login correctly, a message box gives you the option of adding a user account for the login. This example adds a user account for the NWWriter1 account with db_datareader and db_datawriter roles. Select the new user in the DatabaseName Users list to enable the Database Object Permissions tab, and click the tab to display a page with lists of tables, views, stored procedures, and user-defined functions. Click an object to display current permissions for the user in the ObjectName Permissions list, which shows permissions inherited from the user's predefined roles (see Figure 22.38). NWWriter1 has implicit permissions for read-write table operations, but not References (DRI). You can add or remove References (DRI) by marking or clearing the only enabled check box. Figure 22.38. The Database Object Permissions page displays inherited permissions and permissions you add by marking enabled check boxes.  The db_datareader and db_datawriter roles don't have implicit permissions to execute stored procedures. Thus you must grant execute permissions for each stored procedure to each group or user of an upsized project (see Figure 22.39). Figure 22.39. Be sure to grant explicit execute permissions for all SELECT stored procedures to read-only and read-write users.  Repeat steps 6 through 9 to add more logins, database users, and their object permissions.
Closing the form closes the connection to the server instance. The MSDE User Manager isn't a replacement for SQL Server's Enterprise Manager, but it fulfills the basic role of Access 2000's Database Security tool. Note  | Visual Studio Magazine's "Manage MSDE Security" article, which describes the Visual Basic 6.0 and Visual Basic .NET versions of the utility, is at http://www.ftponline.com/vsm/2003_06/magazine/columns/databasedesign/. |
|
