Team LiB
Previous Section Next Section

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.

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.

Sub AddLoginAndUserToDBRole(srvName As String, _ 
    dbsName As String, _
    lgnName As String, _
    usrName As String, _
    dbrName As String)
On Error GoTo AddLogin_Trap
Dim srv1 As SQLDMO.SQLServer
Dim lgn1 As SQLDMO.Login
Dim usr1 As SQLDMO.User
Dim cnn1 As ADODB.Connection
   
'Instantiate a SQLServer object, and connect
'using integrated security
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvName
   
'Remove prior user and login accounts, if they exist,
'by first removing user object and then removing
'login object
srv1.Databases(dbsName).Users(usrName).Remove
srv1.Logins(lgnName).Remove
   
'Add login by instantiating a login object, giving it a
'name, assigning it a default database, setting its
'password, and adding it to a server's Logins collection
Set lgn1 = New SQLDMO.Login
lgn1.Name = lgnName
lgn1.SetPassword "", "password"
srv1.Logins.Add lgn1
   
'Add a user by instantiating it, giving it a name,
'assigning a corresponding login, and adding it to
'a database's Users collection
Set usr1 = New SQLDMO.User
usr1.Name = usrName
usr1.Login = lgn1.Name
srv1.Databases(dbsName).Users.Add usr1
   
'Assign database permissions to user by adding
'the user to fixed database role
srv1.Databases(dbsName). _
    DatabaseRoles(dbrName).AddMember usr1.Name
   
AddLogin_Exit:
Set usr1 = Nothing
Set lgn1 = Nothing
srv1.DisConnect
Set srv1 = Nothing
Exit Sub
   
AddLogin_Trap:
If Err.Number = -2147199728 Then
'User does not exist
    Resume Next
ElseIf Err.Number = -2147206330 Then
'lngCanSelectOnly still logged on
    Set cnn1 = New ADODB.Connection
    cnn1.Open "Provider=sqloledb;Data Source=(local);" & _
        "Initial Catalog=SecurityDemo1; " & _
        "User Id=lgnCanSelectOnly;" & _
   
        "Password=password;"
    cnn1.Close
    Set cnn1 = Nothing
    Resume Next
ElseIf Err.Number = -2147200496 Then
'Login does not exist
    Resume Next
Else
    Debug.Print Err.Number, Err.Description
End If
   
End Sub 

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.

Sub TestSelectPermissionLogin()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim srv1 As SQLDMO.SQLServer
Dim srvName As String
Dim dbsName As String
Dim lgnName As String
Dim usrName As String
Dim dbrName As String
   
'Create lgnName on srvName and usrName in dbsName
'with dbrName database role
srvName = "(local)"
dbsName = "SecurityDemo1"
lgnName = "lgnCanSelectOnly"
usrName = "usrCanSelectOnly"
dbrName = "db_datareader"
AddLoginAndUserToDBRole srvName, dbsName, _
    lgnName, usrName, dbrName
   
'Connect using lgnName login to dbsName database with
'password equal to password
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=sqloledb;Data Source=" & srvName & ";" & _
    "Initial Catalog=" & dbsName & "; " & _
    "User Id=" & lgnName & ";" & _
    "Password=password;"
   
'Open Pic_Addresses table and return record count
Set rst1 = New ADODB.Recordset
rst1.Open "SELECT * FROM Pic_Addresses", cnn1, _
    adOpenKeyset, adLockOptimistic, adCmdText
Debug.Print "Count of records is: " & rst1.RecordCount & _
    ".  Attempt to open succeeds when user belongs to " & _
    "db_datareader role."
   
'Close and remove from memory recordset and connection
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
   
'Instantiate a SQLServer object, and connect
'using integrated security; drop usrCanSelectOnly from
'db_datareader role for SecurityDemo1
srvName = "(local)"
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvName
srv1.Databases(dbsName). _
    DatabaseRoles(dbrName).DropMember usrName
srv1.DisConnect
Set srv1 = Nothing
   
'Connect using lgnName login to dbsName database with
'password equal to password
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=sqloledb;Data Source=" & srvName & ";" & _
    "Initial Catalog=" & dbsName & "; " & _
    "User Id=" & lgnName & ";" & _
    "Password=password;"
   
'See if usrCanSelectOnly can select after it is removed  'from db_datareader role
Set rst1 = New ADODB.Recordset
On Error Resume Next
rst1.Open "SELECT * FROM Pic_Addresses", cnn1, _
    adOpenKeyset, adLockOptimistic, adCmdText
   
'Simple trap for no SELECT permission error
If Err.Number = -2147217911 Then
    MsgBox "No SELECT permission for Pic_Addresses " & _
    "table.  See DBA for assignment of SELECT permission " & _
    "to user account: usrCanSelectOnly.", vbCritical, _
    "Programming Microsoft Access 2003"
    Exit Sub
End If
   
End Sub

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.
Sub AddRemoveSQLUsers()

Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim str1 As String
   
'Specify a connection for a database to which
'you want to grant the user access with a login
'that can manage logins
cnn1.Open "Provider=sqloledb;Data Source=(local);" & _
    "Initial Catalog=Northwind;" & _
    "User Id=sa; Password=password;"
   
'Create a SQL Server login
str1 = "EXEC sp_addlogin " & _
    "@loginame = 'Access11User', " & _
    "@passwd = 'password', " & _
    "@defdb = 'Northwind'"
cnn1.Execute str1
   
'Create a user for the login
str1 = "EXEC sp_grantdbaccess 'Access11User'"
cnn1.Execute str1
   
'Open a recordset of users for the database
'to which cnn1 points; print login and
'user names
rst1.Open "EXEC sp_helpuser", _
    cnn1, adOpenKeyset, adLockOptimistic
Debug.Print "After adding user"
Do Until rst1.EOF
    Debug.Print rst1("LoginName"), rst1("UserName")
    rst1.MoveNext
Loop
   
'Remove user and associate login added above
str1 = "EXEC sp_revokedbaccess 'Access11User' " & _
    "EXEC sp_droplogin @loginame = 'Access11User'"
cnn1.Execute str1
   
'Open a recordset of users for the database
'to which cnn1 points; print login and
'user names
rst1.Close
rst1.Open "EXEC sp_helpuser", _
    cnn1, adOpenKeyset, adLockOptimistic
Debug.Print vbLf & "After removing user"
Do Until rst1.EOF
    Debug.Print rst1("LoginName"), rst1("UserName")
    rst1.MoveNext
Loop
   
'Clean up objects
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
   
End Sub

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.


Team LiB
Previous Section Next Section