Managing Microsoft SQL Server Security with Microsoft Access

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Rick Dobson, Ph.D.
CAB, Inc.

December 2001

Applies to:
     Microsoft® Access 2000 and later
     Microsoft SQL Server 7.0 and later

Summary: This article describes how to manage SQL Server security with Microsoft Access both through the Access user interface and by using the SQL Distributed Management Objects (SQL-DMO) type library from Microsoft Visual Basic for Applications (VBA). (36 printed pages)

Download Sssec.exe.

Contents

Introduction
Overview of SQL Server Security Issues
Using the Database Security Menu Command
Programming SQL Server Security
Conclusion
About the Author

Introduction

This article primarily targets Microsoft® Access developers who started taking advantage of the new data definition capabilities introduced with Access 2000 for building Microsoft SQL Server™ solutions. You have probably noticed two points regarding security in your initial encounters with creating SQL Server solutions. First, security is not optional as it is with Jet. Second, the SQL Server security model differs from the one in Access. Even if you have a rudimentary understanding of SQL Server security, this article can equip you to fine-tune your SQL Server security settings to protect the resources in your custom solutions.

SQL Server database administrators (DBAs) can also benefit from this article, because Access rapid application development tools offer a way to expedite solution development for their databases. With the content from this article, DBAs can readily tap SQL Server security management without returning to Enterprise Manager for graphical tools or running Transact SQL (T-SQL) scripts from Query Analyzer. By learning how to manage SQL Server security from Access, SQL Server DBAs gain the opportunity to build more tightly integrated solutions faster than with other approaches.

Your understanding of this article will benefit substantially from a grasp of the basics of how to build SQL Server solutions with Access. Several sources are available for database developers and DBAs seeking to gain a familiarity with Access techniques for building SQL Server solutions. My two books cover this topic in detail. Professional SQL Server Development with Access 2000 introduces all the major aspects of building SQL Server solutions, but it focuses mostly on Access 2000 and SQL Server 7 because of the timing of its release. My book titled Programming Microsoft Access Version 2002 includes two lengthy chapters on creating SQL Server 2000 solutions with Access 2002. I also authored a series of articles on Access development techniques for SQL Server for SQL Server Magazine. You can learn more about the books and gets links to the articles online from the ProgrammingMSAccess.com Web site.

One area that I have not written on is a known incompatibility between early versions of Access 2000 and SQL Server 2000. Access 2000 is fully compatible with SQL Server 7, but SQL Server 2000 shipped after the release of Access 2000. A new file format for SQL Server 2000 and other issues created various incompatibilities between Access 2000 and SQL Server 2000. Learn more about these issues from the Knowledge Base Article ACC2000: Incompatibility Issues Between Access 2000 Projects and SQL Server 2000.

This article looks at SQL Server 7.0 and SQL Server 2000 security from three perspectives. It begins with an overview of SQL Server security concepts, focusing on those topics that you can readily manage with Access. These topics are sufficient for the development of multi-user applications with different groups of users enjoying distinct permissions for database objects. Next, it demonstrates how to administer SQL Server security from the Database Security menu command (Tools menu, Security submenu) in Access 2000. For organizations that have not upgraded to Access 2002, this menu offers an interface very similar to security dialog boxes available in Enterprise Manager, a SQL Server client component. The latter part of the article presents programmatic solutions based on the manipulation of SQL-DMO (SQL Distributed Management Objects) by Microsoft Visual Basic® for Applications (VBA). Since SQL-DMO is a hierarchical object model like many Microsoft Office object models, Access developers are likely to feel comfortable with it after they learn the objects, properties, methods, and events. Therefore, Access developers may find it a familiar way to program SQL Server security. In any event, managing SQL Server security directly from Access 2002 requires a programmatic approach since the Database Security menu command is not available.

Overview of SQL Server Security Issues

The following overview of SQL Server security divides the topic into four major areas. The presentation begins with an introduction to authentication—the process for verifying that a user has the right to access a database server. Through Enterprise Manager, SQL Server offers two ways for verifying the credentials of prospective users. The initial overview topic compares and contrasts these techniques.

Next, the focus shifts to the credentials users present for making a connection with a server. These credentials are logins. Two types of logins correspond to authentication modes for SQL Server. While a login corresponds to what we commonly call a user, the term login is more precise because it refers to the credential for entering a server.

The third security overview is about users. SQL Server reserves the term users to characterize database users. Just as any server will typically have many logins, it is typical for one login to have many user accounts associated with it—one for each database to which the login can gain access. Logins and users can each have fixed roles that denote clusters of preset permissions. You can control the behavior of logins and user accounts by controlling their membership in fixed server roles and fixed database roles, respectively.

The fourth security area addresses permissions for individual database objects and user-defined database roles.

Throughout this article, I refer to SQL Server instances. SQL Server 2000 introduces the capability to create multiple instances of SQL Server on the same computer. Security settings apply to individual instances. With SQL Server 7, you could only have one default instance of SQL Server.

Authentication

Authentication describes a process by which a credential gains access to a particular SQL Server instance and any databases associated with the account. The credential is a login that specifies who the individual is. The credential works like a key for a lock. If the key fits the lock, the person using it gets to the contents of any occupied rooms that the lock guards. Any one SQL Server instance will typically have multiple locks that different keys fit. Each key grants access to a different set of databases within an instance of SQL Server. Figure 1 represents this notion.

Figure 1. A login credential acts like a key for letting users into an instance of SQL Server. Each login can have a different set of partially overlapping databases associated with it.

SQL Server supports two styles of authentication—SQL Server and Microsoft Windows® (or Windows NT® in SQL Server 7.0). These two styles denote whether SQL Server or Windows verifies the login. With Windows authentication, users present a trusted account to SQL Server. This is an account that Windows verifies, but SQL Server knows the account's name. Windows authentication lets a user log on to Windows and SQL Server by entering just one password. With SQL Server authentication, SQL Server both verifies and tracks the login credentials. With either type of authentication, SQL Server must know about the login credential.

There are pros and cons to both styles of authentication, but the trend is toward Windows authentication. Windows has more robust login validation processes, and it saves a DBA from having to manage login accounts. On the other hand, not all operating systems supporting SQL Server enable Windows validation. For example, the Microsoft Data Engine (MSDE) that shipped with Microsoft Office 2000 and the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) that ships with Office XP both run on Microsoft Windows 98, which does not support Windows authentication. In fact, SQL Server 7.0 and MSDE run on Microsoft Windows 95. Some organizations may prefer the capability to control access to database resources at the DBA level instead of a more global Windows administrator level.

Any given SQL Server instance can have either of two authentication mode settings offered by Enterprise Manager (I will mention a third option later in this article). SQL Server documentation assigns the term mixed mode to SQL Server instances that support both Windows and SQL Server authentication. If a server supports just Windows authentication, then the server has Windows (or Windows NT) authentication mode. MSDE installs by default with mixed mode authentication. By contrast, MSDE 2000 installs by default with Windows authentication mode. Developers and administrators using Enterprise Manager can graphically change the mode setting. In addition, any application that can program SQL-DMO is able to alter the authentication mode setting for a server.

Logins and Fixed Server Roles

An Access project contains a Database window similar to the traditional one for Jet database solutions. However, an Access project connects via an OLE DB connection to a SQL Server database rather than a Jet database. You specify a type of login on the Data Link Properties dialog box for an Access project. From the Database window menu, select File, and then select Connection to open this dialog box.

The Data Link Properties dialog box is how you specify the database server, login, and database for the OLE DB connection underlying an Access project. You can designate the login in one of two ways. First, you can choose to use Windows NT integrated security. This specifies Windows authentication. For a Data Link Properties dialog box with the option button (also called a radio button) for this option selected, do not specify a login name or password. This is because Windows verifies the user ID at the time a user logs into Windows. By choosing to use integrated security, you instruct the Access project to pass the Windows login along to SQL Server when it attempts to create its OLE DB connection. Second, if you click the option button for using a specific user name and password, then you designate a SQL Server login. The user name entry must be a standard SQL Server login name—that is, one maintained by SQL Server. A password is optional, but I highly recommend you use a password since it provides an additional level of security beyond the login's name.

Figure 2 contrasts the appearance of the DataLink Properties dialog box for a Windows login with a SQL Server login. Both dialog boxes in the figure connect to the same server instance, CAB2000, and database, SecurityDemo1. The dialog box on the left demonstrates how to specify a Windows login. The dialog box on the right shows the format for designating a SQL Server login.

Figure 2. The Data Link Properties dialog box on the left denotes a Windows login specification, while the one on the right illustrates the format for a SQL Server login.

While a login is critical for authorizing the creation of an OLE DB connection for an Access project, the login by itself does not necessarily empower an Access project to perform any tasks on the server or with a database. It is the login membership in fixed server roles that enables an Access project's connection to perform server functions, such as the creation of new databases and the management of logins. Depending on the version of SQL Server to which an Access project connects, there are seven or eight fixed server roles. SQL Server 7.0 and MSDE offer seven fixed server roles, and SQL Server 2000 and MSDE 2000 make eight fixed server roles available. SQL Server Books Online provides thorough documentation on roles, including T-SQL statements for assigning and dropping logins from membership in a role. See the Roles topic in Books Online for an introduction to fixed server roles and a corresponding set of roles for database permissions, such as viewing and writing to tables.

The following table presents a short summary of fixed server role names with a brief description. You can execute the sp_helpsrvrole system stored procedure to list the fixed server role names. Execute the sp_srvrolepermission system stored procedure to enumerate precise functions for each fixed server role. There are changes in the fixed server roles between the 7.0 and 2000 versions of SQL Server. For example, bulkadmin is a new role with SQL Server 2000. In addition, the DROP DATABASE statement was available exclusively to the sysadmin role in SQL Server 7.0, but SQL Server 2000 gives members of the dbcreator role permission to use the statement too.

Fixed server role name	Fixed server role description
sysadmin	Execute any server or database statement.
serveradmin	Administration of server, such as configuration and shutdown.
setupadmin	Administration of linked servers and designation of stored procedures to run at startup.
securityadmin	Administration of logins, passwords, and permission to create new databases.
processadmin	Execute KILL statement.
dbcreator	Administration of creation, changing, renaming, and dropping of databases.
diskadmin	Administration of disk files.
bulkadmin	Execute BULK INSERT statements.

The sa login is a special SQL Server login. This login belongs to the sysadmin group, and it can therefore perform any function on a server. SQL Server creates the login during installation, and you cannot remove it. Immediately after installation, the sa login has a null password. You should assign a password to enforce security for your database server—especially for servers with a mixed mode authentication setting. Recall that servers with a Windows authentication mode do not accept SQL Server logins.

When you install any version of SQL Server on Windows 98 or Windows Millennium Edition computers, your server always has a mixed mode authentication setting so that it can accept SQL Server logins. The default authentication mode varies for SQL Server 7.0 and MSDE versus SQL Server 2000 and MSDE 2000 on Windows 2000 and Windows NT computers. With SQL Server 7.0 and MSDE, the default installation process leaves the server with a mixed mode authentication setting. In contrast, SQL Server 2000 and MSDE 2000 default installation selections leave the computer in Windows authentication mode. In addition, the installation process for the 2000 edition sets the Windows Administrator user as a Windows login that has membership in the sysadmin fixed server role. Therefore, this login, just like the sa login, has full control over the server, but the Administrator user is a Windows login.

Database Users and Fixed Database Roles

After getting into an instance of SQL Server, most typical users will want to work with one or more databases. For this reason, SQL Server has a mechanism for connecting logins to databases. Just as any one server can have one or more logins, each login can have one or more user accounts associated with it. An individual user account can belong to just one login (with two exceptions). SQL Server documentation refers to user accounts as database users (or users). Each database user can grant a login access to an individual database.

SQL Server assigns database ownership and permissions to database users. SQL Server enables the assignment of permissions within a database at two distinct levels. First, you can assign one or more preset clusters of permissions to a user. SQL Server documentation refers to these clusters as fixed database roles. I call them clusters of permissions because they can group permissions for many database objects. For example, one fixed database role (db_datareader) enables you read data from any table in a database—not just one or two specific tables. Second, you can assign permissions at the level of individual database objects. SQL Server permits you to mix the two approaches so that a user can be a member in one or more fixed database roles and still have specific permissions for individual database objects. I discuss the management of permissions for individual database objects in the next section.

The two special users that can associate with more than one login are the dbo and guest users. The dbo user is any member of the sysadmin fixed server role that creates an object within a server, such as a database or a table within a database. A user for a login that does not belong to the sysadmin fixed server role can also create database objects, such as a table.

The visibility of objects in the Database window of an Access project depends on whether the project's login is a sysadmin fixed server role member. If the login for an Access project is a member of the sysadmin fixed server role, then the Database window shows all objects owned by dbo users without parentheses after their name. For dbo users, all objects owned by other users besides dbo users appear with the name of the user in parentheses after the database object name. When the login for an Access project is not a sysadmin fixed server role member, the names of objects owned by the user for the login appear in the Database window without parentheses. The names of objects owned by dbo users appear with dbo in parentheses after their names. Objects not owned by either the login's user or a dbo user do not appear at all in the Database window.

Most logins have a database user for each database that they access. However, a login can access a database even without a user in the database if the database has a special user. The name of the database user is guest, and the user is for logins without a user account of their own in a database. A guest user does not associate with any individual login. You can assign permissions to the guest user just like any other database user. This allows you to specify the permissions for logins that access a database without a specific database user in the database.

Members of the sysadmin fixed server role have unlimited authority for any database on a server. Therefore, members of this role can access any database whether or not they have a user account in a database.

One quick and easy way to empower users to perform functions within a database is to assign users to fixed database roles. SQL Server has nine fixed database roles, and they are the same in both SQL Server 7.0 and SQL Server 2000. The fixed database role names and brief descriptions appear in the following table. Execute the sp_helpdbfixedrole system stored procedure to enumerate the names of fixed database roles. Invoke the sp_dbfixedrolepermission system stored procedure to generate a result set with the specific permissions for each fixed database role.

Fixed database role name	Fixed database role description
db_owner	Unlimited authority in a database.
db_accessadmin	For adding and removing users from a database.
db_datareader	For selecting from tables and views in a database.
db_datawriter	For inserting, updating, and deleting from tables and views in a database.
db_ddladmin	For executing any SQL Data Definition Language statement (or performing a comparable function graphically) in a database.
db_securityadmin	For managing user member in roles, permission assignments for objects, and database ownership.
db_backupoperator	For backing up and restoring a database.
db_denydatareader	For denying (or revoking) any SELECT permission for a specific database object.
db_denydatawriter	For denying (or revoking) any INSERT, UPDATE, or DELETE permissions for a specific object.

Assigning users to fixed database roles affects the functions that users can perform. You can modify the impact of the fixed database role membership assignments for users with permission assignments for specific database objects. This section ignores this capability for simplicity, but the next section addresses the topic as it examines permissions for individual objects and user-defined database roles. The Database window will not show any tables unless the user for the login of an Access project is a member of the db_datareader fixed database role. A user without membership in the db_backupoperator fixed database role cannot invoke the Backup or Restore menu commands (Tools menu, Database Utilities submenu). Similarly, users cannot create tables or other objects within a database without membership in the db_ddladmin fixed database role. In terms of the Database window, users without membership in the db_ddladmin fixed database role can start to design server-side objects, such as tables and views, by clicking New. However, the newly designed objects will not save to the database.

Members of the db_owner fixed database role can perform all the functions conveyed by membership in any other fixed database role. Since logins can generate databases without membership in the sysadmin fixed server role, members of the db_owner group do not have to be a dbo user. Furthermore, members of the sysadmin fixed server role can perform any function within a database that a db_owner member can perform. In addition, tables and views created by the dbo user have improved visibility relative to tables and views created by a db_owner fixed database role member whose login is not a sysadmin member.

User-defined Database Roles and Permission Assignments

SQL Server security enables the specification of six types of object permissions for individual database objects. Database administrators can grant access to these permissions for individual objects by creating user-defined database roles and adding users to the roles. Then, they can assign the permissions for the individual objects to the user-defined roles. All members of any of these custom roles inherit the permissions for the role. Database administrators can add and drop users from roles after creating them, and administrators can alter the permissions for user-defined roles as well.

The six object permission names and descriptions appear in the following table. You assign these permissions to individual objects within a database. If you want a permission to be available for all objects, then you need to assign the permission to each object within the database. Therefore, when a fixed database role has the capability that you wish, you should consider using it when you want a permission to extend across all the objects in a database. For example, if you want to grant SELECT permission for all tables and views (including those not created yet), it is easier to assign a user to the db_datareader fixed database role. If your application requires restricting SELECT permission to just one or two existing views, then create a user-defined database role, assign SELECT permission for those views, and give the users who need the special permission membership in your user-defined database role.

Permission name	Permission description
SELECT	See rows in a table or a view.
INSERT	Add new rows to a table or view.
UPDATE	Modify row values in a table or view.
DELETE	Remove rows from a table or view.
REFERENCES	Allow foreign key references to primary or unique keys in a table or row-returning user-defined function.
EXECUTE	Execute stored procedure or user-defined function.

Permissions for individual database objects can have three states: granted, denied, and revoked. If you grant permission for an object, the permission's status is granted. Likewise, if you deny permission, the permission is denied. When you revoke permission, you change its setting from either granted or denied to revoked. Revoked statuses neither grant nor deny permission for an object.

Because it is possible for a user to belong to multiple fixed and user-defined database roles, it is possible for permissions to conflict across role memberships. You can use these conflicts to fine-tune your security settings. For example, a user might belong to the db_datareader fixed database role giving them permission to perform SELECT queries for all tables and views in a database. However, you can also deny permission for a salary table so that the user can select from all tables, except the confidential information in the salary table. Similarly, db_denydatareader fixed database role members will not be able to perform a SELECT query against any table even if the user belongs to a user-defined database role that explicitly grants permission to select from a specific table. A denied permission always overrides a granted permission.

SQL Server facilitates security management by assigning permissions to groups of users through their membership in user-defined and fixed database roles. Recall that each user typically refers to an individual login. In turn, logins can refer to individual Windows users. However, a single login can also correspond to a Windows group. Therefore, if you already have a Windows group for which you want to manage security settings uniformly, then create a login for the Windows group. Next, create a user for the login. Any permissions that you assign to the database user applies to all the members of the corresponding Windows group.

Using the Database Security Menu Command

In Access 2000, you can add a new login with the Database Security menu command (Tools menu, Security submenu). Of course, the login for the Access project must have the authority to create new logins. The sa login always meets this requirement. New logins created with the Database Security menu command are for the server to which an Access project connects. The dialog boxes available from the command are the same ones that Enterprise Manager offers for specifying SQL Server security. This section illustrates how to use the command to perform several common SQL Server security management functions.

Creating a SQL Server Login

To create a new login for a SQL Server, invoke the Database Security menu command. From the initial SQL Server Security dialog box, select the Add on the Server Logins tab. This opens the SQL Server Login Properties—New Login dialog box. When creating a new SQL Server login, type a name for the login in the Name text box on the General tab. Select SQL Server authentication, and type the password for the login in the Password text box. Use the Database drop-down box to select the name of a database to which you want the login to have default access. Until you change it, this drop-down box shows the name of the master database, which is a SQL Server system database.

After completing the General tab, select the Server Roles tab. On this tab, select any server roles to which you want your login to belong. For example, if you want users of the login to be able to create databases with the Microsoft SQL Server Database Wizard, then assign the login to the dbcreator role. For an Access project connected to a SQL Server 7.0 or MSDE instance, your login must belong to the sysadmin role to invoke the Drop SQL Database menu command (Tools menu, Database Utilities submenu). With SQL Server 2000 or MSDE 2000, you can invoke this command through login membership in the dbcreator role.

Next, select the Database Access tab. For most logins, you will select at least one database for which you want the login to have access. You can select multiple databases. These database selections correspond to the cylinders behind the key and lock in Figure 1. I discuss this tab in more detail in the next section.

After making all these selections, select OK. Confirm the password for the new login and select OK in the Confirm Password dialog box to complete the creation of the login.

Figures 3a, 3b, and 3c illustrate the SQL Server Login Properties—New Login dialog box selections for creating a SQL Server login named msdn4 that has access to the SecurityDemo1 database. A user connecting to the server with the msdn4 login will be able to use the Microsoft SQL Server Database Wizard to create a database since the login belongs to the dbcreator role.

Figure 3a. The General tab selections for the creation of a SQL Server login

Figure 3b. The Server Roles tab selection that enables a login to create databases

Figure 3c. A Database Access tab that grants a login access to the SecurityDemo1 database

Creating a Windows Login

The process for creating a Windows login is nearly the same as for a SQL Server login. A Windows login is a login in SQL Server that a Windows server authenticates. On the General tab of the SQL Server Login Properties—New Login dialog box, type a two-part name. The first part is the Windows server name, and the second part is the user name for a Windows user ID. Delimit the two parts with a backslash (\). For example, if the local Windows server name is CABLAT and Windows user name is msdn3, then the login name should be CABLAT\msdn3. You do not have to set a password for a Windows login, since Windows verifies the authenticity of the login. However, you do have to make selections for server and database access and permissions. After making the selections you desire for a Windows login, select OK to complete the creation of the new Windows login for the SQL Server. Figure 4 shows the General tab settings for a Windows login like the one described in this paragraph that connects by default to the SecurityDemo1 database.

Figure 4. General tab selections for specifying a Windows login to a SQL Server instance

Creating a Database User Based on a Login

Actually, we already accomplished this task—twice. In assigning a login access to a database (see Figure 3c), SQL Server automatically creates a database user for the login. The name of the user is the same as the SQL Server login name or the Windows login name after the backslash. This convention helps to make it clear to which login a user belongs.

If you do not select one or more databases for a login to access when you create the login, then SQL Server does not create users for the login automatically. To demonstrate creating a user account in a separate step, I created a new login, msdn5, without giving it any database access. To add a user for such a login, open the SQL Server Security dialog box by choosing the Database Security menu command (Tools menu, Security submenu). Then, click the Database Users tab. Next, use the Login name drop-down list to select a login. In this example, I selected the msdn5 login (see Figure 5). Since I discuss database role assignments later, there are no further choices to make. Select OK to confirm the selection, and then close the dialog box. You can edit and delete user accounts from the Database Users tab of the SQL Server Security dialog box.

Figure 5. A step from adding a user for an existing login named msdn5

Assigning Users to Fixed Database Roles

After creating your login, assigning fixed server role memberships, and assigning database access, the next step is to give the corresponding users permissions in a database. If you use the msdn5 login to access the SecurityDemo1 database on the CABLAT server from an Access project, the Database window does not show any user-defined tables or views. This is because the msdn5 user has no user-defined or fixed database role memberships other than public role, and the public role does not have any permissions for the database. All database users automatically belong to the public role, and the role does not get permissions in a new database until a DBA (or developer) assigns them to the public role.

As mentioned above, the easiest way to give a user permissions is to assign the user as a member of one or more fixed database roles. Start this process by opening the SQL Sever Security dialog box to the Database Users tab. Then, select the user for which you want to add a role membership. Next, select Edit. To give the msdn5 user permission to see tables and perform select queries against the tables in the database, select db_datareader from the list of fixed database roles on the General tab (see Figure 6). Then select OK, and close the SQL Server Security dialog box.

Figure 6. A dialog box showing the addition of the msdn5 user to the db_datareader fixed database role

The next time someone opens an Access project based on the msdn5 login, the Database window shows any tables and views belonging to the dbo user in the database. The msdn5 user can also open any of these tables or views. However, if the user tries to enter a new record, edit an existing one, or delete a record, an Access dialog box replies that the dbo user denies permission. This outcome assumes the msdn5 login is not a member of the sysadmin fixed database role.

Creating a User-Defined Data Role

If you want to give several users permissions for just one or two tables, then you need to add users to user-defined database roles instead of fixed database roles. To illustrate this process and extend our current example, let's create a user-defined role named msdn that has SELECT, INSERT, UPDATE, and DELETE permissions for the TABLE1 table in the SecurityDemo1. Add the msdn3, msdn4, and msdn5 users to the user-defined database role. At the conclusion of these tasks, the msdn5 user is to select as well as add, edit, and delete records to TABLE1. However, msdn5 is still unable to change the contents of any other table in the database besides TABLE1.

Again, start by opening the SQL Server Security dialog box from an Access project based on a login with adequate authority to make the changes. For example, any login belonging to the sysadmin fixed server role can accomplish the task. Next, select the Database Roles tab and select Add. Type a name for your new user-defined role in the Name text box on the General tab. Then, with the Standard role option button selected, select Add. In the Add Role Members dialog box, select msdn3, msdn4, and msdn5 (see Figure 7) before selecting OK. Then, select OK again in the Database Role Properties—New Role dialog box. This returns you to the SQL Server Security dialog box. Close the dialog box.

Figure 7. This screen shows the addition of three users (msdn3, msdn4, and msd5) to the msdn user-defined database role

After creating a user-defined role and adding members to it, the next step is to assign permissions to it. Select the Database Roles tab of the SQL Server Security dialog box (if you are not already there). Then, select the msdn role and select Edit. Select Permissions on the General tab of the Database Role Properties—msdn dialog box. Then, scroll down to TABLE1 in the Database Role Properties—msdn dialog box. Select the check boxes for SELECT, INSERT, UPDATE, and DELETE permissions (see Figure 8) before selecting OK to close the Permissions tab and return to the General tab of the dialog box. Unlike most tabbed dialog boxes, these tabs appear as different dialogs boxes, but they have the same dialog box title. Select OK again in the next Database Role Properties—msdn dialog box to confirm your changes to the msdn user-defined database role. Now, you can just close the SQL Server Security dialog box.

Figure 8. This screen demonstrates the designation of SELECT, INSERT, UPDATE, and DELETE permissions for the TABLE1 table for the msdn user-defined database role

You can demonstrate the impact of the new msdn role and the user assignments to it by opening a new Access project based on the msdn5 login. Again you can see and select from TABLE1. However, now the Access project based on the msdn5 login can insert, update, and delete records from TABLE1. Because the msdn5 user belongs to the db_datareader fixed database role, it can also open other tables, such as TABLE2 (see the table listed in Figure 8 just below TABLE1). However, any attempt to change this second table results in an error message because neither the db_datareader fixed database role nor the msdn user-defined database role conveys the permission to alter the table. In fact, if you open another Access project based on the \CABLAT\msdn3 login, the Database window can show just one object—namely, TABLE1. This is because the msdn3 user has permissions for just this one object. Recall that I never added the msdn3 user to the db_datareader fixed database role.

Programming SQL Server Security

SQL-DMO is an Automation application that targets the administration of SQL Server. Since the SQL-DMO is the API for SQL Server Enterprise Manager, you can program anything with SQL-DMO that you can perform graphically with Enterprise Manager, including managing all aspects of SQL Server security. This aspect of SQL-DMO is especially relevant to database solutions using MSDE and MSDE 2000 because they ship without Enterprise Manager. In addition, Access 2002 developers must learn some programmatic solution for managing security because Access 2002 drops the graphical user interface described in the preceding section.

You can program SQL-DMO just like any other COM object from Access. Your VBA project must have a reference to the SQL-DMO library. The name of the object model library is Microsoft SQLDMO Object Library. Add a reference for the library with the References menu command (Tools menu). The DLL for the library ships with MSDE and MSDE 2000. Other SQL Server editions include the DLL and a Help file that you can open directly from the Visual Basic Editor (VBE) window in an Access VBA project. Those using the Microsoft Office XP Developer edition will obtain a copy of SQL Server that includes the SQL-DMO Help file.

Figure 9 presents an excerpt from the overall SQL-DMO model that highlights the hierarchical relationship between objects for the code samples to follow. Notice the SQLServer object is at the top of the hierarchy. Many applications will start by connecting to a SQL Server instance that the application represents with a SQLServer object. Nested within the SQLServer object are a couple of collections, for logins and databases, as well as an individual object, IntegratedSecurity, that offers several properties for configuring security on a SQL Server instance. A subsequent sample will use the IntegratedSecurity object's SecurityMode property to demonstrate how to set the authentication mode for a SQL Server instance. The members of the Databases collection are individual databases, each of which can have its own collections and objects. One of these collections is the Users collection. Another collection is for fixed and user-defined database roles (DatabaseRoles). The membership of individual users in DatabaseRoles collection items determines a user's ability to select and modify items in the Tables and Views collections.

Figure 9. An excerpt from the SQL-DMO object model that highlights selected collections and objects relevant to SQL Server security

Before launching into a discussion of SQL-DMO, it is important to understand that the DLL for SQL-DMO is different in SQL Server 7 than the one in SQL Server 2000. The two SQL-DMO versions are different in at least two ways. First, the SQL Server 2000 version includes new objects that take advantage of the features introduced with SQL Server 2000 as well as selected other features. The SQL-DMO version for SQL Server 2000 additionally enables you to program in a legacy set of objects that reflect the functionality of SQL Server 7.0. The names for the new objects typically end with 2 (for example, SQLServer2 for the new object instead of SQLServer for the legacy object). Second, SQL Server 7.0 cannot process SQL-DMO programs written with the SQL-DMO model for SQL Server 2000 (even when the SQL-DMO programs use the compatible, legacy set of objects). However, SQL Server 2000 can process SQL-DMO programs written with the SQL-DMO model for SQL Server 7.0. This distinction results from a file format issue between versions; the SQL-DMO script syntax does not change between versions.

The differences between versions of SQL-DMO require you to carefully reflect which version you will use for your applications. If you want your SQL-DMO applications to run across an environment with SQL Server 7.0 and SQL Server 2000 servers, you need to develop your SQL-DMO programs with SQL Server 7.0. On the other hand, if it is imperative to take advantage of features introduced with SQL Server 2000, then you must develop your applications on a computer running SQL Server 2000. Since this article introduces SQL-DMO security programming, it uses the SQL-DMO version compatible with SQL Server 7.0. In any event, the code looks the same across versions, except when you are using the new objects introduced with SQL Server 2000.

Logging on to SQL Server

Just as you can log into SQL Server two ways from the Data Link Properties dialog box in an Access project, you can log into SQL Server two ways from SQL-DMO. One way relies on a standard SQL Server login. This is a login that SQL Server maintains and authenticates. When using this approach, your code must pass a server name, login name, and password through SQL-DMO to a server. You can use the server name parameter to refer to different SQL Server instances on a local workstation or another workstation. SQL-DMO also permits you to login by specifying just the server name. In this case, SQL-DMO passes the Windows-authenticated user ID to the SQL Server instance to gain entrance to the server. In order to take advantage of this second approach, you need to set the LoginSecure property of the server to True.

The following listing includes a pair of procedures that demonstrates the syntax for logging into a server instance with a SQL Server login. The first procedure defines three string parameters for the server name (srvname), login name (suid), and password (pwd). Next, it passes these to the second procedure that begins by instantiating a SQLServer object. This object represents a server instance. Then, the second procedure invokes the Connect method for the SQLServer object. This method takes three arguments. The syntax shows the positional format for designating the server name, login name, and password.

Sub CallSQLDMOSQLServerLogin()
    Dim srvname As String
    Dim suid As String
    Dim pwd As String

    'Set arguments for SQL Server login
    srvname = "CABLAT"
    suid = "msdn5"
    pwd = "password"

    'Call procedure for SQL Server login
    SQLDMOSQLServerLogin srvname, suid, pwd

End Sub

Sub SQLDMOSQLServerLogin(srvname As String, _
        suid As String, pwd As String)
    Dim srv1 As SQLDMO.SQLServer

    'Instantiate server
    Set srv1 = New SQLDMO.SQLServer

    'Invoke Connect method for SQL Server login
    srv1.Connect srvname, suid, pwd

    'Clean up
    srv1.Disconnect
    Set srv1 = Nothing

End Sub

The next code sample illustrates the syntax for logging into a server instance with a Windows login based on a Windows user ID. In this second style of connecting to a server, there is no need to specify either a login name or password. SQL-DMO automatically retrieves the Windows user ID and logs the user into the server with the login for the user ID. Specify the LoginSecure property as True before invoking the Connect method. The property's default value is False.

The login process with SQL-DMO is more robust than with Access projects. For example, if a server has a Windows authentication mode, it will reject a SQL Server login when you use an Access project to make the connection. If you use SQL-DMO in the same situation, SQL Server automatically fixes up the login by basing it on a user's Windows user ID. This silent recovery can result in a user logging into a server instance with an identity that is in conflict with the user's expectations. After a login, use the UserName property of any Database object to confirm the identity of a connected user.

Sub CallSQLDMOWindowsLogin()
    Dim srvname As String

    'Set argument for Windows login
    srvname = "CABLAT"

    SQLDMOWindowsLogin srvname

End Sub

Sub SQLDMOWindowsLogin(srvname As String)
    Dim srv1 As SQLDMO.SQLServer

    'Instantiate Server
    Set srv1 = New SQLDMO.SQLServer

    'Set LoginSecure property before invoking
    'the Connect with the server name
    srv1.LoginSecure = True
    srv1.Connect srvname

    'Clean up
    srv1.Disconnect
    Set srv1 = Nothing

End Sub

Changing the Authentication Mode

One major SQL-DMO advantage is that it gives MSDE and MSDE 2000 developers a chance to access features that are unavailable to them without it. This is because Enterprise Manager does not ship with either MSDE or MSDE 2000. For example, the Enterprise Manager SQL Server client component lets an administrator graphically change the authentication mode of a server between Windows mode and mixed mode. Access projects offer no such feature. However, the next pair of procedures lets you change the authentication mode of a server—even without the availability of Enterprise Manager.

The first procedure assigns a value to one parameter and passes it to the second procedure. The parameter designates either of the two authentication modes. The procedure's comments show the name of two intrinsic constants—one for each authentication mode designation. It assigns a mixed authentication mode by default. SQL-DMO allows an authentication mode setting that is actually unavailable from the Enterprise Manager user interface. Namely, you can use SQL-DMO to specify that a server accepts only SQL Server logins.

The second procedure logs in with the user's Windows user ID. Then, it sets the SecurityMode property of the IntegratedSecurity object for a server to the value of the passed constant from the first procedure. If this property value assignment changes the authentication mode, the mode will not actually change until you stop and restart the server. However, invoking the Stop method for a server object does not immediately halt the server. You have to wait for the server to shut down. With a server's Status property, your code can detect when a server reports the shutdown is complete. The procedure uses a loop to wait until the Status value changes to SQLDMOSvc_Stopped. Next, the procedure executes the Start method of the SQLServer object. Invoking this method installs the new authentication mode setting for the server.

Sub CallChangeServerAuthenticationMode()
    Dim constAuth As Byte

    'Set constAuth to
    '   SQLDMOSecurity_Integrated to change to Windows Authentication Mode
    '   SQLDMOSecurity_Mixed to change to Mixed Authentication Mode

    'Set default value for constAuth
    constAuth = SQLDMOSecurity_Mixed

    'Call procedure to change authentication mode
    ChangeServerAuthenticationMode constAuth

End Sub

Sub ChangeSeverAuthenticationMode(constAuth As Byte)
    Dim srv1 As SQLDMO.SQLServer

    'Assign whatever server name you like;
    'default is CABLAT
    srvname = "CABLAT"

    'Instantiate a SQLServer object and connect
    'using integrated
    Set srv1 = New SQLDMO.SQLServer
    srv1.LoginSecure = True
    srv1.Connect srvname

    'Set SecurityMode property for Windows or
    'or Mixed Authentication Mode
    srv1.IntegratedSecurity.SecurityMode = constAuth
    srv1.Disconnect

    'Invoke command to stop server and wait
    'until it stops
    srv1.Stop
    Do Until srv1.Status = SQLDMOSvc_Stopped
    Loop

    'Restart server with mixed security
    srv1.Start True, srvname

    'Clean up
    srv1.Disconnect
    Set srv1 = Nothing

End Sub

Opening an Access Project

You will sometimes prefer to have your SQL-DMO applications programmatically open an Access project. The technique for opening an Access project does not require SQL-DMO, but your advanced SQL-DMO applications can readily benefit from this capability. In fact, this last code sample in this article (just prior to the Summary section) illustrates the application of the technique to help demonstrate the functionality of a new user. The need to open an Access project based on a connection string is common when deploying solutions based on Access projects.

The next sample demonstrates how to open an existing Access project. In the code below, the Access project's filename is msdn_test_security.adp. The code sample permits you to open the Access project with a Windows login based on the current user's Windows user ID or a SQL Server login that the script designates. The code sample demonstrating the programmatic opening of an Access project again relies on two procedures. The list of parameters in the first procedure, CallOpenADPWindowsOrSQLServer, is relatively long. That is because we need to specify a server, a database, an Access project path and file, a SQL Server login and password, and a Boolean variable. The Boolean variable designates the opening of the project based on a Windows login or a SQL Server login. When you designate the use of a Windows login, there is no need to specify a login and password since the Access project will automatically reference the user's Windows user ID. The second procedure uses these inputs to open the Access project.

Before reviewing the second procedure, it will be useful to explain some background on the sample code. First, the procedure to open an Access project is already running in an Access session. Therefore, the procedure needs to open a new instance of Access for the Access project it opens. The sample uses automation to create a new Access session.

Second, any existing Access project always opens with the Data Link Property dialog box settings from the last time that it opened. Therefore, you need to initially open the Access project (with its old settings), and then change the connection settings for the project. While the project is open, you can assign the new settings. When you close the project, the new connection setting will persist until a user changes the settings in the Data Link Property dialog box. (By the way, the Office object model permits you to hide and make unavailable the Connection menu command (File menu), so a user cannot change your connection setting.)

Third, the session holding the opened Access project will persist as long as the variable representing the Access project started by Automation is open. Use a Public statement in the Declaration section at the top of the module to declare the variable representing the session holding the Access project. This gives the session a life as long as the VBA project for the project automating the session. Our sample uses appAccess for the variable name denoting the Access session, and the following declaration at the top of a standard module. Because the declaration needs to be at the top of the module, it does not appear in the listing for either of the two procedures for the sample.

Public appAccess as Access.Application

The second procedure, OpenADPWindowsOrSQLServer, starts with a prompt asking whether to leave the automated Access session open after the procedure concludes. If the user says no, the application assigns the new connection setting to the Access project, and then closes the session with the Access project without giving the user a chance to view it. Next, the project launches a new Access session with the CreateObject function. The argument for this function varies depending on whether you are using Access 2000 or Access 2002 (see the code listing for the proper argument for either version). After creating a session for the Access project, the procedure invokes the OpenAccessProject method to add the Access project to the session. The argument for the method is the concatenated result of the path and filename for the Access project. The explicit designation of a trailing file type of .adp is optional. Once the Access project is open, the procedure resets its connection settings with the OpenConnection method of the CurrentProject object for the automated session. The procedure resets the connection using one of two different connection strings, depending on the value of bolWindowsLogin. This is a Boolean variable set in the first procedure for designating either a Windows login or a SQL Server login for the Access project. The last step in the procedure optionally terminates the automated session for the Access project based on the user's reply to the opening prompt about whether to leave the Access project's automated session open.

Sub CallOpenADPWindowsOrSQLServer()
    Dim srvname As String
    Dim dbname As String
    Dim prpath As String
    Dim prname As String
    Dim suid As String
    Dim pwd As String
    Dim bolWindowsLogin As Boolean

    'Set parameters for routine to open Access project
    srvname = "CABLAT"
    dbname = "SecurityDemo1"
    prpath = "C:\Documents and Settings\Administrator\My Documents\"
    prname = "msdn_security_test"
    suid = "msdn5"
    pwd = "password"

    'This argument controls use of Windows login for
    'current user instead of SQL Server suid and pwd
    bolWindowsLogin = False

    'Call routine to open the prname Access project
    'with either a Windows or SQL Server login
    OpenADPWindowsOrSQLServer srvname, dbname, _
        prpath, prname, suid, pwd, bolWindowsLogin

End Sub

Sub OpenADPWindowsOrSQLServer(srvname As String, dbname As String, _
        prpath As String, prname As String, _
        suid As String, pwd As String, bolWindowsLogin As Boolean)

    Dim bolLeaveOpen As Boolean
    Dim strPrFilePath As String
    Dim sConnectionString As String

    'Keep project open before exiting routine?
    If MsgBox("Do you want to leave project open?", vbYesNo) = vbYes Then
        bolLeaveOpen = True
    End If

    'Instantiate Access session (use .9 for Access 2000
    'and .10 for Access 2002)
    Set appAccess = CreateObject("Access.Application.9")

    'Open project with login and password from last use
    'and show it
    strPrFilePath = prpath & prname
    appAccess.OpenAccessProject strPrFilePath
    appAccess.Visible = True

    'Assign new login for either Windows or SQL Server;
    'add an error trap for attempts to login in the wrong way
    If bolWindowsLogin Then
        appAccess.CurrentProject.OpenConnection _
            "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;" & _
            "PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=" & _
        dbname & ";DATA SOURCE=" & srvname
    Else
        sConnectionString = "PROVIDER=SQLOLEDB.1;INITIAL CATALOG=" & _
            dbname & ";DATA SOURCE=" & srvname
        appAccess.CurrentProject.OpenConnection _
            sConnectionString, _
            suid, pwd
    End If

    'Close Access session if requested, or stop
    'for viewing Data Link Property dialog settings
    If bolLeaveOpen = False Then
        appAccess.CloseCurrentDatabase
        Set appAccess = Nothing
    End If

End Sub

Adding and Removing Logins

There are three types of logins to SQL Server. These types include one type authenticated by SQL Server and another two types authenticated by Windows. The two login types authenticated by Windows are for individual user IDs and for Windows groups. If you already have a group defined in Windows that should enjoy homogeneous login rights and database access, you can readily base a login on the Windows group. This will save you the trouble of re-defining security settings separately for each member of the group.

The demonstration for creating logins shows the syntax for creating a standard SQL Server login and a login based on a Windows group. The approach for creating a login within SQL Server for a Windows group is nearly the same as setting it for an individual Windows user ID. Therefore, the following sample for creating a login for a Windows group applies identically for a Windows user ID.

The sample code for creating logins relies on two procedures. The first procedure merely specifies the parameters for a SQL Server login to the server for which the second procedure will create the new logins. The third procedure is a function procedure that decodes the value of an intrinsic constant to a string denoting the name of the constant.

The second procedure instantiates a login object after connecting to a server. This object is a member of the Logins collection that appears in Figure 9. Next, the procedure sets three properties for the new login: its name (msdn6), default database (SecurityDemo1), and password. Simple property assignments work for the first two properties. To set the password for a SQL Server login, invoke the login's SetPassword method. When you are setting the password for a new login object, use an empty string for the first argument; specify the new password for the new login as the second argument. In our sample, the password for the msdn6 login is the string "password" (without quotes). Complete the addition of the msdn6 login by invoking the Add method for the server's Logins collection and designating the login that you instantiated previously in the procedure.

You can also use the SetPassword method to reset the password for an existing login. The method's first argument for the SetPassword method is for the current password. However, if you do not know the existing password, you can set the first argument to an empty string—just as you do when creating the password for a new login. Creating or modifying a login requires the same fixed server role memberships whether you do it manually or programmatically. Therefore, modifying the password for a login with the SetPassword command requires membership in either the sysadmin or securityadmin fixed server roles. The sample uses the sa login, which is a member of the sysadmin fixed server role.

The next several lines in the second procedure create a new login for the Windows group named msdn_OS_users. In my case, this Windows group contains a selection of three Windows user IDs, but it can be any Windows group on your local or domain Windows server. The process of creating a new login begins by freshly instantiating the lgn1 login. If you attempt to reuse the lgn1 login without instantiating it again, a run-time error reminds you to create a fresh login object. The procedure uses a two-part name for the Windows group. The first part designates the Windows server name for the group, and the second part denotes the group's name on the Windows server. A backslash delimits the two parts. This second login again references SecurityDemo1 as its default database. Since the second login is for a Windows authenticated login, the sample does not need to set a password. However, the sample does need to specify the login's Type property to SQLDMOLogin_NTGroup. This setting denotes an intrinsic constant indicating the login points at a Windows group. There is no need to specify the Type property for a standard SQL Server login because the default value for the property denotes a login authenticated by SQL Server. The addition of the second login concludes with a statement invoking the Add method for the server's Logins collection.

After adding the two new logins, the procedure prints the name and type of each login. The login's Type property references the type of login with an intrinsic constant. To make the output more readily interpretable, the sample's third procedure translates the intrinsic constant's number to its name.

When the second procedure concludes enumerating the members of the Logins collection, it deletes the two new logins, namely msdn6 and CABLAT\msdn_OS_users. The sample demonstrates that you use the Logins collection's Remove method and the Name property of the login that you want to eliminate. To confirm the deletion of the two logins, the procedure enumerates the members of the Logins collection a second time. Open the Immediate window and contrast the two login listings to see the addition and removal of the logins.

Sub CallLoginDemo()
    Dim srvname As String
    Dim suid As String
    Dim pwd As String

    'Set arguments for SQL Server login
    srvname = "CABLAT"
    suid = "sa"
    pwd = "password"

    'Call procedure for SQL Server login
    LoginDemo srvname, suid, pwd

End Sub

Sub LoginDemo(srvname As String, _
        suid As String, pwd As String)
    Dim srv1 As SQLDMO.SQLServer
    Dim lgn1 As SQLDMO.Login

    'Instantiate server
    Set srv1 = New SQLDMO.SQLServer

    'Invoke Connect method for SQL Server login
    srv1.Connect srvname, suid, pwd

    'Instantiate login object
    Set lgn1 = New SQLDMO.Login

    'Add default SQL Server login
    lgn1.Name = "msdn6"
    lgn1.Database = "SecurityDemo1"
    lgn1.SetPassword "", "password"
    srv1.Logins.Add lgn1

    'Instantiate login object again for reuse
    Set lgn1 = New SQLDMO.Login

    'Add login object based on Windows group
    lgn1.Name = "CABLAT\msdn_OS_users"
    lgn1.Database = "SecurityDemo1"
    lgn1.Type = SQLDMOLogin_NTGroup
    srv1.Logins.Add lgn1

    'Enumerate logins after adding logins
    Debug.Print "Logins after adding two new ones"
    For Each lgn1 In srv1.Logins
        Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name
    Next lgn1

    'Remove recently added logins
    srv1.Logins.Remove "CABLAT\msdn_OS_users"
    srv1.Logins.Remove "msdn6"

    'Enumerate logins again
    Debug.Print vbCr & "Logins after removing two new ones"
    For Each lgn1 In srv1.Logins
        Debug.Print DecodeLoginType(lgn1.Type), lgn1.Name
    Next lgn1

    'Clean up
    srv1.Disconnect
    Set srv1 = Nothing

End Sub

Function DecodeLoginType(lgn_type As Byte) As String

    Select Case lgn_type
        Case 0
            DecodeLoginType = "SQLDMOLogin_NTUser"
        Case 1
            DecodeLoginType = "SQLDMOLogin_NTGroup"
        Case 2
            DecodeLoginType = "SQLDMOLogin_Standard"
        Case Else
            DecodeLoginType = "Type out of range"
    End Select

End Function

Creating a Login with a db_datareader User

Adding a login and specifying a default database programmatically does not create a user for the login in the database. Therefore, an Access project based on the new login cannot open the default database. To empower an Access project to work with a database, you must create a user for the login in a database.

The code sample for this concluding section of the article reinforces how to add a standard SQL Server login, but it extends the sample to create a user for that login in the SecurityDemo1 database. The sample also assigns the user to the db_datareader fixed database role. Finally, the sample opens an Access project based on the new add-in. This gives you a chance to confirm the functionality for the new login and the settings for the Data Link Property dialog box of the Access project.

After logging into the server and creating a new SQL Server user named msdn6, the code sample instantiates a new user. Next, it assigns Name and Login properties for the user. The user's Login property denotes the login account to which the user connects—msnd6 in our sample. After completing these steps, the user is ready for addition to the Users collection for a database. Notice the syntax for adding a new user follows a familiar hierarchical syntax. The Add method for the new user applies to the Users collection, which belongs to a database. The database is a member in the Databases collection, which belongs to a server. After creating the user, it takes just one more line to make the user a member of the db_datareader fixed database role. The method for assigning the user to the role is the AddMember method, but the syntax is very similar. In this case, the AddMember method for an item in the DatabaseRoles collection adds the new user to the database role. The method takes a string value that it derives from the Name property for the new user.

Once the security setting for the login and its user is complete, the sample calls the OpenADPWindowsOrSQLServer procedure. A prior sample demonstrated the operation of this procedure. In this application, it opens the msdn_test_security.adp file based on the msdn6 login. You can confirm the functionality for your Access project by opening tables in the database. In addition, you can verify that the Data Link Properties dialog box settings point at the msdn6 login.

If you try to run the MakeLoginWithDatareaderUser a second time, without any intervening steps, your attempt will fail. That is because the msdn6 login will already exist when you try to open it. Your second attempt can fail for other reasons, such as you not closing the Access project based on the msdn6 login. You can write standard VBA error traps to detect these at run time. Similar types of maladies can accompany the operation of the preceding code samples. I purposely avoided discussing error traps to keep the focus on the substantive SQL Server security issues.

Sub MakeLoginWithDatareaderUser()
    Dim srv1 As SQLDMO.SQLServer
    Dim lgn1 As SQLDMO.Login
    Dim usr1 As SQLDMO.User
    Dim srvname As String
    Dim suid As String
    Dim pwd As String
    Dim dbname As String
    Dim prpath As String
    Dim prname As String
    Dim bolWindowsLogin As Boolean

    'Set up selected arguments
    srvname = "CABLAT"
    suid = "sa"
    pwd = "password"
    dbname = "SecurityDemo1"

    'Instantiate server
    Set srv1 = New SQLDMO.SQLServer

    'Invoke Connect method for SQL Server login
    srv1.Connect srvname, suid, pwd

    'Instantiate and add a login object
    'to the srv1 server
    suid = "msdn6"
    Set lgn1 = New SQLDMO.Login
    lgn1.Name = suid
    lgn1.Database = dbname
    lgn1.SetPassword "", pwd
    srv1.Logins.Add lgn1

    'Instantiate and add a user object
    'to the SecurityDemo1 database
    Set usr1 = New SQLDMO.User
    usr1.Name = suid
    usr1.Login = lgn1.Name
    srv1.Databases(dbname).Users.Add usr1
    srv1.Databases(dbname).DatabaseRoles("db_datareader").AddMember usr1.Name

    'Set parameters for routine to open Access project
    prpath = "C:\Documents and Settings\Administrator\My Documents\"
    prname = "msdn_security_test"

    'This argument controls use of Windows login for
    'current user instead of SQL Server suid and pwd
    bolWindowsLogin = False

    'Call routine to open the prname Access project
    'with either a Windows or SQL Server login
    OpenADPWindowsOrSQLServer srvname, dbname, _
    prpath, prname, suid, pwd, bolWindowsLogin

    'Leave objects open for viewing project

End Sub

Conclusion

SQL Server security relies on a different model from the one Access developers use with Jet databases. Nevertheless, SQL Server Books Online carefully documents the rules for SQL Server security. This article translates the general rules in Books Online to specific rules that target the use of Access projects with SQL Server. The examples for the Access project user interface and program code samples covered in this article further clarify how to set security for your SQL Server solutions developed with Access projects.

About the Author

Rick Dobson is an author and a trainer, and has written Programming Microsoft Access 2000, Programming Microsoft Access Version 2002, and Professional SQL Server Development with Access 2000. Rick and his wife Virginia run a seminar practice. You can contact Rick at http://www.programmingmsaccess.com.