Tech Networking
  |  Home |  Articles |  Free Downloads |  Free Code |  Access Links |  SQL Azure |  Products |  Get Premium Software |  Login  |  Register |  Contact Us |   
Skip Navigation LinksHome Page > Articles By Patrick Wood > SQL Azure Database Roles and Users

How to Use Microsoft Access to Manage SQL Azure Database Users and Roles

One of the best things about SQL Azure is how easy it is to manage User security. In this article we will show you how you can use Microsoft Access to add individual Users to Database Roles which makes managing User security much easier.

The Benefits of Using Flexible Database Roles

If you grant and deny permissions to individual database Users instead of using Database Roles you will have to grant and deny all the permissions to your database objects over and over again each time you create a Database User. It is much easier and efficient to grant and deny all the permissions just one time to a Database Role that you create. Then you just have to add new Users to the Role and all the granted and denied permissions are automatically applied to the User.

There are a number of additional advantages to using Database Roles. You can create as many Database Roles as you need and you can add as many Users as you need to a Role. You can add Users to more than one Role. This allows you complete control over all the permissions granted to all the Database Users. You can change a Role's Permissions at any time and the changes will immediately apply to all Users assigned to the Role. If you no longer want to allow access to a View, you just deny the Role all permissions to the View and immediately all the Database Users that are members of that Role can no longer access that View.

This is very helpful when you develop an application for multiple users which have differing access and security requirements. You can set up one Role with Permissions for data entry, another for workers in the financial department, and another for managers. Controlling Permissions with Database Roles enables you to make sure the financial information is only accessed by authorized users, and the managers can see the Reports they need.

Understanding Flexible Database Roles

Before you begin creating SQL Azure Database Roles there are a few things that you need to know. First, SQL Azure has two types of Database roles: fixed Database Roles which have fixed permissions and flexible Database Roles which you can create and for which you can grant and deny permissions as you choose. You can learn more about fixed database roles from these articles: Permissions of Fixed Database Roles (Database Engine) and Understanding SQL Server fixed database roles.

We will be working with the flexible Database Roles which you create and are able to control the permissions granted and denied to the Role. Using this method you need to create the Role before we work with Permissions. And in this case we will also add users to the Role before granting and denying Permissions.

How to Create Flexible Database Roles

The syntax for creating a Database Role is CREATE ROLE role_name [ AUTHORIZATION owner_name ]. If the optional owner_name is not used the User that created the Role will be the owner of the Role. Normally I use the SQL Azure Administrator account to create roles so I do not usually bother with the Authorization statement. So the T-SQL we will use with SQL Azure to create the Role looks like this:


Using a Form to Create Flexible Database Roles

That is just about as simple as you can get. But we can also create a simple Form to make it easier to create a new Role. To do this, I added a textbox named "txtNewRole" with the label caption "Enter New Role Name". I also added a command button named "cmdCreateRole". After making some improvements to the appearance the form looked like this:

Create Roles Form

The code that runs when the "Create New Role" button is clicked first Validates there is a value in the txtNewRole text box, builds a SQL Statement and passes it to the ExecSQLAzureSQL Function. The Function returns a value of "True" to the Sub if the Role was successfully created or "False" if it fails. The second value passed to the procedure is used like a Password to make the code secure. We will explain this later.

Private Sub cmdCreateRole_Click()
    Dim strSQL As String

    'Verify a Role name has been entered.
    If Len(Me.txtNewRole & vbNullString) = 0 Then
        MsgBox "Please enter a name for the new Role.", vbCritical
        'Create the T-SQL to be passed to SQL Azure.
        strSQL = "CREATE ROLE " & Me.txtNewRole

        'Create the new Role.
        '"BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m" is used like a Password to safeguard the ExecSQLAzureSQL Procedure.
        If ExecSQLAzureSQL(strSQL, "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m") = False Then
            MsgBox "The Role was not created. Please try again.", vbCritical
            MsgBox "The Role """ & Me.txtNewRole & """ was created.", vbInformation
        End If
    End If
End Sub

How to Execute T-SQL in SQL Azure Using a Pass-through Query and VBA

The ExecSQLAzureSQL Function uses a temporary Pass-through query in code to "pass" your SQL, without modifying it, directly to the SQL Azure Database specified in the Connection String. This Function can be used any time you need to execute a T-SQL statement in SQL Azure that does not return records. It returns "True" if the SQL is executed successfully and "False" if it fails. The comments in the Function explain what the code is doing.

*Update: My good friend known as "The Smiley Coder" pointed out that this procedure needed to be made secure. He was absolutely right. So I have modified the procedure below. To secure the connection string and other code whenever I provide databases to clients for users I give them a mde or accde database file so they cannot see the code. Plus I often pass the connection string as a Procedure Argument. Then to provide even more security I use a Procedure Argument like a password the way the "strIn" Argument is used in ExecSQLAzureSQL below.

'Public Function ExecSQLAzureSQL
'Place this Procedure in a Standard Module.
'Executes the SQL using a Pass-through Query.
'The SQL is executed in the SQL Azure database in the connection string.
'This procedure is for "Action Queries" SQL that does not return records.
Public Function ExecSQLAzureSQL(strSQL As String, strIn As String) As Boolean
On Error GoTo ErrHandle

    Dim db As DAO.DATABASE
    Dim qdf As DAO.QueryDef

    ExecSQLAzureSQL = False 'Default Value.
    'This line ensures the code only runs if the "Password" Argument "strIn" is correct.
    If strIn = "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m" Then

        Set db = CurrentDb

        'Create a temporary unnamed Pass-through QueryDef. This is a
        'practice recommended in the Microsoft Developer Reference.
        'The order of each line of code must not be changed or the code will fail.
        Set qdf = db.CreateQueryDef("")

        'Use a function that returns the Connection string to the SQL Azure database.
        'Change obfuscatedFunctionName to a different name for Security.
        qdf.Connect = obfuscatedFunctionName("Wb_gR%/PD\-k&yZq~j>l")

        'Set the QueryDef's SQL as the strSQL passed in to the procedure.
        qdf.SQL = strSQL

        'ReturnsRecords must be set to False if the SQL does not return records.
        qdf.ReturnsRecords = False

        'Execute the Pass-through query.
        qdf.Execute dbFailOnError

        'If no errors were raised the query was successfully executed.
        ExecSQLAzureSQL = True
    End If

    'Cleanup for security and to release memory.
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Exit Function

    MsgBox "Error " & Err.Number & vbCrLf & Err.Description _
    & vbCrLf & "In procedure ExecSQLAzureSQL"
    Resume ExitHere

End Function

How to Get the Connection String to a SQL Azure Database

The obfuscatedFunctionName Function returns the Connection String. The SQL is executed in the SQL Azure Database specified in the Connection String: "DATABASE=MySQLAzureDatabaseName;".

'It is best to change the name of this procedure for better security for your use.
'The strIn Argument value, "Wb_gR%/PD\-k&yZq~j>l", is used like a Password to keep
'unauthorized users from getting your Connection String. You should also change it
'to suit you before you use it in a distributed application.
Public Function obfuscatedFunctionName(strIn As String) As String
    If strIn = "Wb_gR%/PD\-k&yZq~j>l" Then
        obfuscatedFunctionName = "ODBC;" _
            & "DRIVER={SQL Server Native Client 11.0};" _
            & ",1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
        obfuscatedFunctionName = vbNullString
    End If
End Function

How to Use a Form to Add a User to a SQL Azure Database Role

We can add Users to the Role we have created using the sp_addrolemember System Stored Procedure in this manner:

EXEC sp_addrolemember 'MyRoleName', 'MyUserName';

We can modify the Form we created earlier to add Users to Roles. I added a text box named "txtRoleName" and another named "txtUserName". After adding a command button named "cmdAddUserToRole" this is how the Form appears:

Manage Users and Roles Form

The following code was added to the cmdAddUserToRole button's click event to add the User to the Role:

Private Sub cmdAddUserToRole_Click()

    Dim strRole As String
    Dim strUser As String

    'Verify a Role name has been entered.
    If Len(Me.txtRoleName & vbNullString) = 0 Then
        MsgBox "Please enter a name for the Role.", vbCritical
        strRole = Me.txtRoleName

        'Verify a User name has been entered.
        If Len(Me.txtUserName & vbNullString) = 0 Then
            MsgBox "Please enter a name for the User.", vbCritical
            strUser = Me.txtUserName

            'Now that we have both a Role and a User Name we
            'can call a Function to add the User to the Role.
            Call AddUserToSQLRole(strRole, strUser, "X~K<2%fM>Tn5ejxJ$R&ZEyUzh")
        End If
    End If
End Sub

Using a Function to Add a User to a SQL Azure Database Role

The command button's code calls the AddUserToSQLRole Function which we need to place in a Standard Module. This Function passes the necessary T-SQL to the ExecSQLAzureSQL Function we used earlier. One of the basic principles of good programming practice is DRY (Don't Repeat Yourself). It is better to write one Procedure that does the same job repeatedly and reliably than it is to write the same code over and over again. Using the DRY principal you write less code and make fewer mistakes. It also makes your code easier to debug, manage, understand, and reuse in other applications.

*Update: To provide better security we have added an Argument to this Procedure to use like a Password as we did in the ExecSQLAzureSQL and obfuscatedFunctionName Procedures.

Public Function AddUserToSQLRole( _
    strRole As String, _
    strUser As String, _
    strIn As String _
    ) As Boolean

    Dim strSQL As String

    AddUserToSQLRole = False 'Default Value

    'This line ensures the code only runs if the "Password" Argument "strIn" is correct.
    If strIn = "X~K<2%fM>Tn5ejxJ$R&ZEyUzh" Then

        'Build the T-SQL that will add the User to the Role.
        strSQL = "EXEC sp_addrolemember '" & strRole & "', '" & strUser & "';"

        'Call the Function to execute the SQL.
        If ExecSQLAzureSQL(strSQL, "BO_sR&ai>xc%ZwE_UdVe~9^DPBp1m") = True Then
            AddUserToSQLRole = True

            'Inform the User of Success.
            MsgBox "User """ & strUser & """ was added to the """ _
                & strRole & """ Role.", vbInformation
            'Inform the User of Failure.
            MsgBox "User """ & strUser & """ was NOT added to the """ _
                & strRole & """ Role." & vbCrLf & vbCrLf _
                & Space(30) & "Please try again.", vbCritical
        End If
    End If
End Function


We have examined how to create SQL Azure Database Roles and add Users to those Roles. We are now prepared to control with precision which users have access to the objects in SQL Azure by using SQL Azure Permissions. We will address that crucial subject in another article.

Get the Code

You can download the code used in this article from our Free Code Samples page. Or you can view the code here.

Login Status: Not logged in.
Login      Free Registration

  Call Us for a Free Consultation




Site Navigation

Skip Navigation Links.
Return to Top