As Microsoft Access developers who are used to the Jet engine begin to create SQL Server and Microsoft Data Engine (MSDE) solutions with Access 2000, they'll need to learn a new security model. For example, unlike security for Jet database files, SQL Server security distinguishes between login accounts and user accounts. Understanding the basics of SQL Server security for tables provides a foundation for understanding more advanced security topics involving tables and other database objects. Security is important for any multiuser application in which different users have different levels of permissions for database objects. This month's column drills down on how to use Access projects to control the basics of SQL Server security for tables in multiuser applications. I include a tutorial with a series of demonstrations that assume a knowledge of how to create SQL Server databases and Access projects.
Planning Your Application
When you plan a multiuser application that uses Access projects, be aware that you'll need an Access project (.adp) file for each user because .adp files can't be shared. In contrast, classic Access multiuser applications let multiple users share one Access database (.mdb) file. Also, you typically need another Access project file for the database and any Access objects under development because the application developer usually isn't also a user of the application. All these project files connect to the same SQL Server database. You can use SQL Server security settings to control access to an application's functionality and the visibility of tables to different users.
Creating the Developer's Project and the Application's Database
Let's launch a multiuser application for contact management by creating a new Access project that in turn depends on a new SQL Server database. Use the sa login or another member of the sysadmin role to create the database. Using this type of login ensures that you have permission to create a table and perform other administrative functions, such as create new login accounts for a database. Name the database SSMagTablesSQL and its initial Access (developer's) project SSMag_Admin.adp. After creating the Access project and database, populate the database with a table. Listing 1 contains a user-defined stored procedure for an Access project that creates a table and populates it with records. Dropping an earlier version of a database object, such as a table, and creating a new one is useful as you refine your security settings because you create a new version of the object; you can then apply security settings to the new version without needing to first remove the object from the database.
Login and User Accounts
Permissions for a table derive from built-in (SQL Server documentation calls these fixed) and custom (or user-defined) security roles. Users derive permissions for database objects by virtue of their membership in roles; a user account can belong to more than one role. For example, a user belonging to the fixed db_datareader role can browse all user tables in a database. (For more information about roles and SQL Server security, see Michael D. Reilly, Certifiably SQL, "Security and Permissions," January 2000.)
Login accounts provide access to a SQL Server or MSDE database server. User accounts accept permissions that let the accounts perform actions on the objects in a specific database. One login account can map to multiple user accounts in different databases on a server. However, a login account can have no more than one user account per database.
Now, create login and user accounts for a user. You can create a new login that maps to a user account by choosing Tools, Security, Database Security from an Access project's Database window menu. This selection opens the dialog box that Screen 1 shows, containing a list of logins in the current server. Click Add to open the SQL Server Login Properties - New Login dialog box, then complete the information for a login account called sqlmag1, as Screen 2 shows. SQL Server security automatically assigns the login name from the form in Screen 2 to the user account. The login account is for a connection to the database server for the current Access project.
Before the user account associated with the login can connect to a database, you need to click the Database Access tab and select one or more databases. Select SSMagTablesSQL, as Screen 3 shows. Unless you designate a specific database role, the sqlmag1 user account inherits just the settings for the Public role. Click OK, and type the login's password into the Confirm Password dialog box to complete the creation of the login and user accounts. Click OK again to close the SQL Server Security dialog box.
Testing a New Login with a New Project
Next, create a new Access project for a user by choosing File, New on the Database window menu and double-clicking Project (Existing Database) on the General tab of the New dialog box. Name the Access project file SQLMag_User.adp. Next, the Data Link Properties dialog box appears. In the server name box, enter the name of the server that manages your database.
Then, you can select either Windows NT integrated security or a specific username and password. Let's use the specific name and password option. Enter the login account's name (sqlmag1) and password where the dialog box asks for a username and password. Then, click the down arrow in the Select the database drop-down list box, which shows a list of databases, including the one you just created, SSMagTablesSQL. Highlight that database name, and click OK to open a new project connected to the database.
When you click Tables on the Objects bar in the project's Database window, no tables display, but the Database window shows the stored procedure name for Listing 1. When you double-click the stored procedure name, Access responds with a message saying it denies EXECUTE permission for the stored procedure. You can't see the table or run the stored procedure because the Public role—the only one sqlmag1 belongs to—doesn't have SELECT permission for the table or EXECUTE permission for the stored procedure.
Managing User Accounts and Roles
You can take a couple of approaches to solving the invisible table problem. Before you can edit security settings in a database, you need to log in to it with an account that can manage security, such as the sa login. You need to assign the sqlmag1 user account to a role with at least SELECT permission on table1. If an application has a limited number of users with no security differences among them, you can alter the permissions for the Public role to include SELECT permission for table1. But because all user accounts belong to the Public role, any new user accounts you add automatically inherit that permission.
Another approach is to assign the sqlmag1 user account to the db_datareader role. You can select this role at the time you create a user account in the database roles section in Screen 3. (You'll need to scroll the db_datareader role into view to select its check box.) Or, after a user account exists, you can edit it from the Database Users tab of the SQL Server Security dialog box in Screen 1. Let's take this path because the sqlmag1 user account already exists. Highlight the user account name, and click Edit to open a Data User Properties dialog box. Then, select db_datareader to add SELECT permission for all user-defined tables in the database. Click OK twice to exit from the open dialog box and commit the assignment of a user account to the fixed database role db_datareader.
Creating Custom Roles
Sometimes the fixed database roles aren't granular enough for an application's requirements. For example, no fixed database role grants UPDATE permission without INSERT permission just for table1. The fixed database role db_datawriter enables both permissions for all user-defined tables. The solution in this scenario is to create a custom role that contains the precise permissions you need (SELECT and UPDATE for just table1).
Creating a custom role is a two-step process. First, you add a new role and assign it a name by clicking Add on the Database Roles tab of the SQL Server Security dialog box. Then, type table1_datareader/updater as the name for the role in the Name box on the Database Role Properties dialog box, and click OK. Select the new role name on the Database Roles tab, and click Edit. (You can also add existing database users to this role by clicking Add in this screen.) Then, click Permissions on the Database Roles Properties dialog box to open another dialog box, which contains a Permissions tab. Scroll down through the objects until you find the one you want to set permissions for. Then, select the check boxes for the permissions (SELECT and UPDATE) that you want a role to have, as Screen 4 shows.
After completing the role definition, you can populate the role with user accounts. You can create a new user account and add it to the role or assign an existing user account to the role. For this example, you can create a new user account named sqlmag2 by using a process nearly identical to the one you used for sqlmag1. In the General tab of the Login Properties box, change the login name to sqlmag2 from sqlmag1. In the Database Access tab, scroll down in the database roles box to the new role name, table1_datareader/updater, and select it. Then, confirm your choices by exiting as you did for sqlmag1.
You're now ready to complete the example. Open SQLMag_User.adp from another or the same computer. Confirm that the Data Link Properties dialog box connects through the sqlmag1 login. Notice that you can see table1 in the Database window. If you double-click the name, the table opens and shows you its records. However, you can only select records. If you try to change a column's value in a row, a message appears saying Access denies UPDATE permission. If you choose File, Connection from the Database window menu, you can log in to the SSMagTablesSQL database as sqlmag2. With this login account, you can select and update records in table1.
If you plan to build multiuser SQL Server applications with Access 2000, it's essential to master SQL Server security. Access projects don't support user-level security, which is common in multiuser applications involving Access database (.mdb) files. Instead, SQL Server relies on logins, users, and roles. You can now start controlling SQL Server security through an Access project interface, but there's much more to learn. Access and new SQL Server developers can use this foundation for learning more about SQL Server security.