Programming Login and User Accounts

SQL Server has a different security model than the one covered for Jet databases in Chapter 10. In addition, the Access project UI changed from Access 2000 to Access 2003 to remove some screens for manually controlling SQL Server security. As a consequence, the programmatic solutions for managing SQL Server security are more important than ever.

There are at least two programmatic interfaces for managing SQL Server security. The first of these is T-SQL. Using T-SQL to administer SQL Server security is often straightforward because T-SQL includes many commands tailored for managing security. In addition, Books Online offers numerous detailed samples that illustrate the use of T-SQL for programming database security. SQL-DMO, a hierarchical programming language for SQL Server, is another programmatic interface you can use to manage SQL Server security. Your experience with the Office hierarchical models transfers readily to SQL-DMO programming of SQL Server security. This section emphasizes SQL-DMO programming, but it includes some examples of T-SQL programming as well.

SQL Server security is a huge topic. The goal of this section is to acquaint you with selected SQL Server security issues. Once you have this foundation, you can advance your knowledge of the topic as much as your circumstances warrant .

Setting Up the SQL Server Security Demonstrations

This book's presentation of SQL Server security programming techniques relies on a new database, SecurityDemo1, which has a pair of tables, Pic_Addresses and Employees . This simple database design will help you understand the basics of security programming. The security code samples all operate from the Access project for this chapter. These samples demonstrate how to programmatically control the security for one database from an Access project connected to a second database. Chapter 10 showed the same kind of programming technique for Jet security.

Several heavily commented procedures in the Access project for this chapter create the SecurityDemo1 database and its two tables. The main procedure names are CallCreateDBOnFilePath , CreatePic_Addresses3 , and CreateEmployees . These procedures borrow from and sometimes extend the programming methods presented in Chapter 11 and earlier in this chapter. Run CallCreateDBOnFilePath before either of the other two main procedures. CallCreateDBOnFilePath calls two others: one that drops a prior version of the SecurityDemo1 database if it exists, and another to create a new copy of the SecurityDemo1 database. When you run CallCreateDBOnFilePath , make sure there are no active users for the SecurityDemo1 database, such as another Access project connected to the database. You can run the other two procedures in either order. They create the Pic_Addresses and Employees tables in the SecurityDemo1 database. The CreatePic_Addresses3 procedure relies on the prior installation of the Chapter11SQL database on your server, and the CreateEmployees procedure requires that you have the Chapter12SQL database attached to your server.

The SecurityDemo1 database file is included with the companion content for this book. If you prefer, you can attach the sample procedures to your server instead of running them to create the sample security database with its tables.

Creating Login and User Accounts with SQL-DMO

Creating a new SQL Server user for a database requires several steps. First, you have to add a login account. This account permits an individual to log on to the server. SQL-DMO represents a login account with a Login object. This object is a member of the Logins collection that belongs to a SQLServer object. Second, you can create a user account for a database. This user account should reference the login account that you just created. SQL-DMO represents a user account with a User object. This object is a member of the Users collection that belongs to the Database object. Third, you can assign the user account to a database role. This lets the user account inherit the permissions assigned to the role by SQL Server (for fixed database roles) or you (for custom roles). SQL-DMO represents a database role with a DatabaseRole object. This object is a member of the DatabaseRoles collection that belongs to a Database object.

The following sample shows a general procedure for creating a new login account in a server with a corresponding user account in a database on the server. The procedure also makes the user a member in a fixed database role. You must supply five arguments to target the procedure's behavior. The srvName argument designates the server for the login account. The dbsName argument specifies the database name for the user account. The lgnName and usrName arguments designate the names for the login and user accounts. The dbrName argument is the name of a database role. The procedure makes the user account a member of this role.

The procedure has a main flow and an error trap flow. After creating a SQLServer object that points at the server named srvName , the procedure's main flow immediately attempts to remove a prior user account with the name of usrName and a prior login account with the name lgnName . The error flow accounts for various run-time errors that can occur when you attempt to remove a Login or a User object. For example, the object might not exist.

The procedure starts to create a new login account by instantiating a login object ( lgn1 ). Then it assigns a name to the object. Unless you explicitly specify otherwise , all logins have a SQL Server standard type by default. This type property is for SQL Server authentication. Two other login Type property settings can designate a login for Windows NT authentication. The sample invokes the SetPassword method to assign the login a password equal to the string password. You can override this default password in the procedure. Regardless of whether you change this password, users can change it once you make the login account available to them. For example, users can invoke the Tools, Security, Set Login Password command in an Access project to change their password. After designating the login properties, the code adds the new Login object to the Logins collection for the server instantiated at the start of the procedure.

Note	If you change the password setting for the login in this procedure, you should revise it in the other procedures (discussed next ) that reference it.

Next, the procedure moves on to creating a new user account. The procedure begins this process by instantiating a new User object. The next two lines of code assign a name property to the user and associate the user with a login, which is the login just created. After specifying these core user properties, the procedure appends the User object to the Users collection for the database named in dbsName .

The procedure's final step before exiting is to add the new user to a database role. This step lets the user inherit the permissions that belong to the role. The AddMember method of the DatabaseRole object enables the assignment of a user to a role. The argument takes a string with the name of the user that it adds to a role.

 SubAddLoginAndUserToDBRole(srvNameAsString,_  dbsNameAsString,_ lgnNameAsString,_ usrNameAsString,_ dbrNameAsString) OnErrorGoToAddLogin_Trap Dimsrv1AsSQLDMO.SQLServer Dimlgn1AsSQLDMO.Login Dimusr1AsSQLDMO.User Dimcnn1AsADODB.Connection     'InstantiateaSQLServerobject,andconnect 'usingintegratedsecurity Setsrv1=NewSQLDMO.SQLServer srv1.LoginSecure=True srv1.ConnectsrvName     'Removeprioruserandloginaccounts,iftheyexist, 'byfirstremovinguserobjectandthenremoving 'loginobject srv1.Databases(dbsName).Users(usrName).Remove srv1.Logins(lgnName).Remove     'Addloginbyinstantiatingaloginobject,givingita 'name,assigningitadefaultdatabase,settingits 'password,andaddingittoaserver'sLoginscollection Setlgn1=NewSQLDMO.Login lgn1.Name=lgnName lgn1.SetPassword "", "password" srv1.Logins.Addlgn1     'Addauserbyinstantiatingit,givingitaname, 'assigningacorrespondinglogin,andaddingitto 'adatabase'sUserscollection Setusr1=NewSQLDMO.User usr1.Name=usrName usr1.Login=lgn1.Name srv1.Databases(dbsName).Users.Addusr1     'Assigndatabasepermissionstouserbyadding 'theusertofixeddatabaserole srv1.Databases(dbsName)._ DatabaseRoles(dbrName).AddMemberusr1.Name     AddLogin_Exit: Setusr1=Nothing Setlgn1=Nothing srv1.DisConnect Setsrv1=Nothing ExitSub     AddLogin_Trap: IfErr.Number=-2147199728Then 'Userdoesnotexist ResumeNext ElseIfErr.Number=-2147206330Then 'lngCanSelectOnlystillloggedon Setcnn1=NewADODB.Connection cnn1.Open "Provider=sqloledb;DataSource=(local);" &_  "InitialCatalog=SecurityDemo1; " &_  "UserId=lgnCanSelectOnly;" &_      "Password=password;" cnn1.Close Setcnn1=Nothing ResumeNext ElseIfErr.Number=-2147200496Then 'Logindoesnotexist ResumeNext Else Debug.PrintErr.Number,Err.Description EndIf     EndSub

Testing User Accounts

The preceding sample, which was based on the AddLoginAndUserToDBRole procedure, needs another procedure to call it. At a minimum, this second procedure must supply values for the sample that creates the login and user accounts. The following sample accomplishes this, and it verifies the operation of a user account with membership in the db_datareader role. This role authorizes the ability to use a SELECT statement with any table or view in a database.

The sample procedure for this section, TestSelectPermissionLogin , performs several tasks . First, it invokes the AddLoginAndUserToDBRole procedure. Using the arguments passed to the procedure in the preceding sample, the code creates a login named lgnCanSelectOnly with a corresponding user named usrCanSelectOnly . The user belongs to the SecurityDemo1 database and is a member of the db_datareader fixed database role. It is common to name the user corresponding to a login with the same name as the login. However, using different names helps to highlight the unique role of the Login object and the User object in this sample.

After creating the login and user, the code starts to use them. It begins by making a connection to the SecurityDemo1 database on the local server. Notice that you use the value of lgnName (rather than usrName ) for the user ID in the connection string. Next, the code opens a recordset with a T-SQL SELECT statement for the Pic_Addresses table. This is one of the two tables in the SecurityDemo1 database. To confirm the selection, the procedure prints the number of records in the Pic_Addresses table to the Immediate window. Then the procedure closes the recordset and connection before removing both of them from memory.

The success of the attempt to select from the Pic_Addresses table depends on the status of the usrCanSelectOnly user as a member of the db_datareader role. By dropping the User object from the role, you can invalidate an attempt by the usrCanSelectOnly user to select from the Pic_Addresses table. The next segment of code in TextSelectPermissionLogin drops the user from the db_datareader role and then tries to execute the T-SQL statement that previously succeeded. An inline error trap catches the error from the attempt to open the recordset. If the error is the one that points to a missing SELECT permission, the procedure opens a message box informing the user and suggesting she contact the DBA.

 SubTestSelectPermissionLogin() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimsrv1AsSQLDMO.SQLServer DimsrvNameAsString DimdbsNameAsString DimlgnNameAsString DimusrNameAsString DimdbrNameAsString     'CreatelgnNameonsrvNameandusrNameindbsName 'withdbrNamedatabaserole srvName= "(local)" dbsName= "SecurityDemo1" lgnName= "lgnCanSelectOnly" usrName= "usrCanSelectOnly" dbrName= "db_datareader" AddLoginAndUserToDBRolesrvName,dbsName,_ lgnName,usrName,dbrName     'ConnectusinglgnNamelogintodbsNamedatabasewith 'passwordequaltopassword Setcnn1=NewADODB.Connection cnn1.Open "Provider=sqloledb;DataSource=" &srvName& ";" &_  "InitialCatalog=" &dbsName& "; " &_  "UserId=" &lgnName& ";" &_  "Password=password;"     'OpenPic_Addressestableandreturnrecordcount Setrst1=NewADODB.Recordset rst1.Open "SELECT*FROMPic_Addresses",cnn1,_ adOpenKeyset,adLockOptimistic,adCmdText Debug.Print "Countofrecordsis: " &rst1.RecordCount&_  ".Attempttoopensucceedswhenuserbelongsto " &_  "db_datareaderrole."     'Closeandremovefrommemoryrecordsetandconnection rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing     'InstantiateaSQLServerobject,andconnect 'usingintegratedsecurity;dropusrCanSelectOnlyfrom 'db_datareaderroleforSecurityDemo1 srvName= "(local)" Setsrv1=NewSQLDMO.SQLServer srv1.LoginSecure=True srv1.ConnectsrvName srv1.Databases(dbsName)._ DatabaseRoles(dbrName).DropMemberusrName srv1.DisConnect Setsrv1=Nothing     'ConnectusinglgnNamelogintodbsNamedatabasewith 'passwordequaltopassword Setcnn1=NewADODB.Connection cnn1.Open "Provider=sqloledb;DataSource=" &srvName& ";" &_  "InitialCatalog=" &dbsName& "; " &_  "UserId=" &lgnName& ";" &_  "Password=password;"     'Seeif usrCanSelectOnly can select after it is removed  'fromdb_datareaderrole Setrst1=NewADODB.Recordset OnErrorResumeNext rst1.Open "SELECT*FROMPic_Addresses",cnn1,_ adOpenKeyset,adLockOptimistic,adCmdText     'SimpletrapfornoSELECTpermissionerror IfErr.Number=-2147217911Then MsgBox "NoSELECTpermissionforPic_Addresses " &_  "table.SeeDBA for assignment of SELECT permission " &_  "touseraccount:usrCanSelectOnly.",vbCritical,_  "ProgrammingMicrosoftAccess2003" ExitSub EndIf     EndSub

Creating Login and User Accounts with T-SQL

You need to invoke two T-SQL system stored procedures to create a new user in a database based on a SQL Server login. The sp_addlogin system stored procedure can add a login for accessing a SQL Server instance if you are invoking the procedure from a connection with a user ID that has authority to process security accounts, such as a login in the sysadmin fixed server role. The sp_addlogin has one required argument and a maximum number of six arguments. The required argument is @loginame (the argument name is not a typo; it is spelled with a single n), whose value names the login account created by the system stored procedure. The @passwd argument value is a password for the login. If you do not set this argument, then your new login has a null password. The @defdb argument sets the default database for the new login. The default database is the database to which an account connects immediately after connecting to a server. The samples in this chapter assign a user-defined database to the @defdb argument for a new login. The default value for this argument is the master database, which is a system-defined database that controls the operation of a SQL Server instance. Other sp_addlogin arguments allow you to manage more specialized features of logins. Again, you only have to set one argument ( @loginame ) for the sp_addlogin system stored procedure.

Note

A system stored procedure is a built-in stored procedure that SQL Server supplies for performing tasks. Chapter 11 describes how to create stored procedures from an Access project. These are user-defined stored procedures. System stored procedures are system-defined stored procedures. Since system stored procedures begin with an sp_ prefix, it is good practice to avoid using this prefix as the name for your user-defined stored procedures.

After creating a login, you need to give it database access so that it will have a user account in a database. The sp_grantdbaccess system stored procedure performs this function. This procedure takes as many as two arguments, but if the user name is the same as the login name, then you can use a single argument to denote the user name. In addition, you do not have to explicitly designate argument names, you can just trail sp_grantdbaccess with the name of the user. When a user name is something other than the corresponding login name, you must explicitly specify both arguments with a comma delimiter . The @loginame argument denotes the login to which a user corresponds. The @name_in_db argument value denotes the user name. Specify @loginame before @name_in_db .

Just as adding a user takes two system stored procedures, so does removing a login with a user account in a database. In fact, the process of dropping a login can require more than two invocations of system stored procedures. T-SQL requires that you remove all user accounts corresponding to a login before you can successfully drop a login. Invoke the sp_revokedbaccess system stored procedure to remove each user account for a login that you want to drop. This procedure takes a single argument, which is the name of the user account that you want removed from a database. After removing all the user accounts in any database for a user, you can reference the sp_droplogin system stored procedure to eliminate a login from the security accounts for a database. As with the sp_revokedbaccess procedure, the sp_droplogin procedure requires just one argument. In this case, the argument is the name of the login to drop.

The following procedure listing for the AddRemoveSQLUsers VBA procedure illustrates an approach for applying the system stored procedures for managing login and user accounts with T-SQL. The declarations at the top of the procedure declare and instantiate Connection and Recordset objects. The VBA procedure performs two main tasks. First, it creates a login with a corresponding user account for the Northwind database. This is the database that provides a context for manipulating login and user accounts. Second, the VBA procedure removes the user and login accounts created in the first step. After each step, the procedure enumerates the user accounts in the Northwind database.

The AddRemoveSQLUsers procedure begins by declaring and instantiating cnn1 as a Connection object and rst1 as a Recordset object. Through the use of an Open method, the procedure points cnn1 at the Northwind database with sa as the login. Because this login belongs to the sysadmin fixed server role, it can manipulate logins and users. The Execute method for a Connection object permits you to invoke a SQL statement. This method works for T-SQL as well as Jet SQL. You can construct a T-SQL statement to invoke a system stored procedure, such as sp_addlogin , by preceding the statement with the EXEC T-SQL keyword. After opening the cnn1 object, the procedure listing shows the syntax for invoking the sp_addlogin system stored procedure to create a login named Access11User. Next, the code sample uses the same general approach to invoking the sp_grantdbaccess system stored procedure. This application of sp_grantdbaccess creates a user named Access11User. With the sp_helpuser system stored procedure, the sample generates a recordset based on the users in the Northwind database. The sample code enumerates these users by their login and user names. After listing the users in the Immediate window, the sample revokes the Access11User in the Northwind database and drops its corresponding login. To confirm the effect, the procedure again lists the users in the database. In the second listing of users, there are only two users as opposed to three immediately after the addition of the Access11User (see Figure 13-4).

Figure 13.4: The Immediate window showing the users in the Northwind database after adding the Access11User and then removing the user.

 SubAddRemoveSQLUsers() Dimcnn1AsNewADODB.Connection Dimrst1AsNewADODB.Recordset Dimstr1AsString     'Specifyaconnectionforadatabasetowhich 'youwanttogranttheuseraccesswithalogin 'thatcanmanagelogins cnn1.Open "Provider=sqloledb;DataSource=(local);" &_  "InitialCatalog=Northwind;" &_  "UserId=sa;Password=password;"     'CreateaSQLServerlogin str1= "EXECsp_addlogin " &_  "@loginame='Access11User', " &_  "@passwd='password', " &_  "@defdb='Northwind'" cnn1.Executestr1     'Createauserforthelogin str1= "EXECsp_grantdbaccess'Access11User'" cnn1.Executestr1     'Openarecordsetofusersforthedatabase 'towhichcnn1points;printloginand 'usernames rst1.Open "EXECsp_helpuser",_ cnn1,adOpenKeyset,adLockOptimistic Debug.Print "Afteraddinguser" DoUntilrst1.EOF Debug.Printrst1("LoginName"),rst1("UserName") rst1.MoveNext Loop     'Removeuserandassociateloginaddedabove str1= "EXECsp_revokedbaccess'Access11User' " &_  "EXECsp_droplogin@loginame='Access11User'" cnn1.Executestr1     'Openarecordsetofusersforthedatabase 'towhichcnn1points;printloginand 'usernames rst1.Close rst1.Open "EXECsp_helpuser",_ cnn1,adOpenKeyset,adLockOptimistic Debug.PrintvbLf& "Afterremovinguser" DoUntilrst1.EOF Debug.Printrst1("LoginName"),rst1("UserName") rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing cnn1.Close Setcnn1=Nothing     EndSub

Adding a login and user that map to a Windows user or Windows group follows a set of steps similar to that for adding a login and user that SQL Server manages . The sole difference is that you invoke the sp_grantlogin system stored procedure instead of the sp_addlogin system stored procedure. With sp_grantlogin , you can create a login for a Windows user or Windows group account. The sp_grantlogin system stored procedure takes a single argument, which is the name of the Windows account for the user or group. In the AddRemoveWindowsUsers procedure within Chapter13.adp, you can see the syntax for designating a Windows user or Windows group account. You must denote the account with a two-part name. Use a backslash (\) to delimit the parts . The first part denotes the server name, and the second part represents the user name or group name. In the AddRemoveWindowsUsers procedure, the argument CabSony1\PMA11 points at the PMA11 Windows user on the CabSony1 Windows server. After replacing the sp_addlogin with sp_grantlogin , the process for managing logins and users based on SQL Server logins versus Windows user and Windows group accounts is the same. Because the AddRemoveWindowsUsers procedure is nearly identical to the listing for the AddRemoveSQLUsers procedure, you can refer to Module1 in Chapter13.adp for the full listing for the AddRemoveWindowsUsers procedure. In order to run the AddRemoveWindowsUsers procedure, you will need to update the code to reflect your local Windows server and a Windows user account on it.