This article is the fourth and final in a series about administering SQL Server security from Microsoft Access projects. The first three articles ("Access Granted," December 2000, "Securing SQL Server Tables," January 2001, and "Homegrown Security Solutions," February 2001) provide an overview of SQL Server security and demonstrate how to manually control it from Access projects. This article shows you how to program SQL Distributed Management Objects (SQL-DMO) from an Access project to perform three common tasks: creating logins, adding database users, and assigning users to database roles. By programmatically exposing a selected subset of security features, you can remove the built-in Access project security interface and customize the security information that a user can view. Access offers several ways—such as selecting View, Toolbars, Customize—to customize its built-in menus.
SQL-DMO is the DMO library that installs with SQL Server 2000, SQL Server 7.0, and the Microsoft SQL Server Data Engine (MSDE) that ships with Microsoft Office 2000. For a couple of reasons, SQL-DMO is particularly interesting for Access developers who use MSDE. First, MSDE doesn't include SQL Server Enterprise Manager, which is the GUI for administering SQL Server. However, Microsoft used SQL-DMO to program Enterprise Manager, so Access developers can use SQL-DMO programming to deliver subsets of Enterprise Manager's capability. Second, many Access developers are familiar with hierarchical object models from their programming of Access and other Office object models. Therefore, SQL-DMO's hierarchical object model is likely to be more intuitive to Access programmers (after they learn SQL-DMO's objects and capabilities) than the procedurally oriented T-SQL.
Adding Logins to a Server
A login grants entry to a SQL Server without necessarily enabling access to any databases on the server. To add a login to a SQL Server, you can use SQL-DMO from the VBE window of an Access project. Start by creating a new Access project; you can specify any server, login, or database. SQL-DMO lets you forge an independent connection to a SQL Server based on a new login that is distinct from the one for an Access project. Optionally, you can use the login for an Access project to authenticate the SQL-DMO program to the SQL Server. In either case, the SQL-DMO program's login must have appropriate roles for managing security. (See "Access Granted" and "Securing SQL Server Tables" for a quick review of those roles and their relationship to security settings.)
Create a new module by selecting Modules from the Objects bar in the Database window of your Access project and clicking New. Before you can program SQL-DMO, you need a reference to the SQL-DMO object library. First, choose Tools, References, then select Microsoft SQLDMO Object Library from the available references in the References dialog box. Click OK to create the reference.
Listing 1 shows two procedures for creating a new login. You can run these procedures from the module in the Visual Basic for Applications (VBA) project associated with your Access project. The first procedure, call_login_adder, sets three parameters for a new login. These parameters define the login by specifying its name, default database, and password. The call_login_adder procedure calls the second procedure, login_adder, and passes the parameters to it. The login_adder procedure then creates the new login.
Programmatically creating a new SQL Server login involves three steps. First, you must connect from your SQL-DMO program to the SQL Server on which you want to create the login. Second, you must create an instance of a new SQL-DMO login object, assign values to its parameters, and create its password by invoking the Login object's SetPassword method. Third, you need to add the new login to the Logins collection for the server.
The login_adder procedure starts by declaring SQLServer and Login SQL-DMO objects. Note that the SQLDMO prefix on the object names in the Dim statements unambiguously specifies the library in case a VBA project references more than one library that has objects named Login or SQLServer. The procedure uses the SQLServer object srv1 to connect to a SQL Server named cablat. (You'll need to change the server name to the appropriate name for your computing environment.) After creating the srv1 SQLServer pointer, the program invokes the Connect method for srv1 to link to the cablat server. The procedure uses the sa login with no password. You can use any login that has sufficient authority to perform the tasks in a procedure.
After the program forges a connection with a server, the procedure instantiates the Login object as lgn1 and assigns its Name and Database properties. Designating a default database doesn't necessarily grant the login access to the database, so the login needs a user account to access a database (as I explain in the next section). To set the password for a Login object, you use the SetPassword method, which takes two parameters. The first parameter is the initial password, which is an empty string when you're creating a new login. The second parameter is a string designating the login's new password (which is the third parameter that call_login_adder passes to the login_adder procedure). After defining the login, the procedure uses the Add method to append the login to the cablat server's Logins collection.
By default, for SQL Server authentication, the Name property for a Login object takes a single-tiered name, such as cabxli. However, you need a two-tiered name, such as CABLAT\cabxli, to designate a user or group account authenticated through Windows 2000 or Windows NT security. To create a login for user or group accounts authenticated by Windows instead of SQL Server, assign a value to the login's Type property. The Type property's default value is SQLDMOLogin_Standard. Assign SQLDMOLogin_NTUser or SQLDMOLogin_NTGroup if you're creating a login for an individual user or group account that Win2K or NT manages. When creating a login for a Windows user or group account, you don't need to set the login's password because Windows authenticates the login. In fact, SQL-DMO ignores the invocation of the SetPassword method for a login that has a Type property setting of SQLDMOLogin_NTUser or SQLDMOLogin_NTGroup.
Adding a Database User
To gain access to a database, a login needs access to a guest user account or a specific user account in the database. User accounts are members of a Users collection. SQL-DMO refers to an individual member of a Users collection as a user. Whereas the Logins collection belongs to the SQLServer object, a Users collection belongs to each database on a server. Because any login can access a database through the guest user account, the guest account doesn't reference a specific login as all other user accounts (except the dbo user) do. Although not mandatory, a good practice is to give a user account the same name as the login that it references. This practice clearly identifies the login for a user account.
Listing 2 shows two procedures for creating a new user in the SQL Server Northwind database. The first procedure, call_add_user, offers two different paths through an If...Then...Else statement for invoking the second procedure, add_user. The path in the If clause is appropriate for creating a guest user account because it doesn't designate a login for the new user. The other path passes a login_name along with a user_name for the new user. The call_add_user procedure lets you set the user's name before the If...Then...Else statement. Consistent with good naming practice, the first procedure sets the login name for a user account equal to the user name when the procedure designates a login name for a user. You need to alter the code for this assignment if you follow another convention. The second procedure creates the new user according to the parameters the first procedure passes to it.
The add_user procedure accepts as many as two arguments. The first argument, user_name, is mandatory; the second, login_name, is optional. If the first procedure attempts to set up a guest account, the second procedure interprets the second argument as an empty string. An empty string for the second argument lets add_user generate a user account with no specific corresponding login, as is necessary for a guest account.
The add_user procedure divides the process of creating a user into three steps. First, add_user makes the connection to the cablat server. Second, the procedure instantiates a new User object, usr1, and assigns values to its Name and Login properties. Third, add_user appends the newly instantiated User object to the Users collection for the Northwind database on the server. (As before, revise the server and database names as necessary for your environment.)
Assigning User Permissions
Without explicit role assignments, database users have only those permissions that belong to the public role because all users belong to this role. To obtain a special set of permissions, a user must belong to one or more fixed database roles or user-defined roles, as I discussed in "Securing SQL Server Tables" and "Homegrown Security Solutions." SQL-DMO includes a DatabaseRoles collection, which belongs to the Database object. This collection's members include all the fixed database roles and any existing user-defined database roles. You can assign permissions to a user by invoking the AddMember method for a DatabaseRole object. Follow the method with an argument denoting the name of the user you want to add to the role.
As you do when you add a login and user, you must first establish a connection to a server before adding a user to a role. Listing 3 shows the syntax for adding the cabxli user to the sql_mag_role user-defined role for the Northwind database. You can use this method to add a user to a role immediately after you append a User object to a database's Users collection but before you close the connection to a server. Invoking the method eliminates the need for the extra step of creating a new connection to a server.
The procedures I describe in this article can serve as the building blocks of a security administration component in your systems. These procedures demonstrate how you can use SQL-DMO to create logins, add users to databases for those logins, and assign users to fixed and user-defined database roles. These are three critical elements that you might want to provide a better interface for so that supervisors and administrators can more easily manipulate security objects in your custom SQL Server database systems.