Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

A weblog about Microsoft Access, SQL Azure, and SQL Server Tips and Code Samples
Browse by Tags · View All
Access Tips 45
Microsoft Access 45
VBA 41
database 33
Access 33
Code 32
Software development 31
Microsoft Access Software Development 26
Free Access Downloads 25
Software 25

Archive · View All
October 2010 4
September 2008 4
June 2009 4
January 2008 3
July 2009 3
June 2007 2
May 2009 2
March 2011 2
July 2010 2
August 2011 2

Patrick Wood's Blog

Using Microsoft Access to Manage SQL Azure Database Users and Roles

Sep 6 2011 4:45PM by Patrick Wood   

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:

CREATE ROLE MyRoleName

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:

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 Sub then informs us of the success or failure to create the Role.

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
    Else
        'Create the T-SQL to be passed to SQL Azure.
        strSQL = "CREATE ROLE " & Me.txtNewRole
        
        'Create the new Role.
        If ExecSQLAzureSQL(strSQL) = False Then
            MsgBox "The Role was not created. Please try again.", vbCritical
        Else
            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.

'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) As Boolean
On Error GoTo ErrHandle

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

    ExecSQLAzureSQL = False 'Default Value

    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 the obfuscatedFunctionName's 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

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

ErrHandle:
    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 10.0};" _
            & "SERVER=tcp:MyServerName.database.windows.net,1433;" _
            & "UID=MyUserName@MyServerName;" _
            & "PWD=MyPassword;" _
            & "DATABASE=MySQLAzureDatabaseName;" _
            & "Encrypt=Yes"
    Else
        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:

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
    Else
        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
        Else
            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)
        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.

Function AddUserToSQLRole(strRole As String, strUser As String) As Boolean

    Dim strSQL As String
    
    AddUserToSQLRole = False 'Default Value

    '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) = True Then
        AddUserToSQLRole = True
        'Inform the User of Success
        MsgBox "User """ & strUser & """ was added to the """ _
            & strRole & """ Role.", vbInformation
    Else
        '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 Function

If your code fails the cause may be a failure to make the connection to SQL Azure. Often the failure may just be an intermittent and temporary issue that can occur for a number of reasons. So it is best to encourage the user to try again because the second attempt will often succeed.

Summary

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.

 


Republished from Access Easy Tips [18 clicks].  Read the original version here [32134 clicks].

Patrick Wood
102 · 2% · 521
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • Wow. I never thought that we could manage SQL Azure roles and users via Access. Thanks.

    commented on Dec 4 2011 4:33PM
    Paras Doshi
    17 · 10% · 3265

Your Comment


Sign Up or Login to post a comment.

"Using Microsoft Access to Manage SQL Azure Database Users and Roles" rated 5 out of 5 by 1 readers
Using Microsoft Access to Manage SQL Azure Database Users and Roles , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]