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.

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.

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;".

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:

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.

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.