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 > Create SQL Azure Database Users

How to Create SQL Azure Database Users With Microsoft Access VBA

We are going to show you how you can use a pass-through query in Access to create Azure SQL Database Users using Access VBA. Why do we need SQL Azure Database User Accounts? User Logins alone do not allow you to access Database Objects such as Tables, Views, and Stored Procedures. That is, unless you use your Azure SQL Database Administrator account with Access databases that you distribute to users. That would be living very dangerously indeed! So before your database is ready for distribution, a Database User Account needs to be created for each individual Access User. We can do that with VBA. After a Database User is created then we can grant the Database User the necessary permissions to Azure SQL Database Views and Stored Procedures.

Database users must be created in the database in which they will exist because the "USE" statement can only work for the current database in a Azure SQL Database. So to create a Database User we must use a query that runs in the Database in which we want to create the User.

When we have a Login Name we need to choose a Database User Name to use. It would be confusing, to me at least, to log in using "JoeDeveloper" and work as Database User "SamCodeSlinger". So my normal practice is to create a Database User with the same name as a Login Name. We demonstrated how to create logins in our article How to Use Microsoft Access to Create Logins in a SQL Azure Database.

If we were using SQL Server Management Studio (SSMS) or the Windows Azure Management Portal we could create a Database User as shown with the following Transact-SQL (T-SQL):




But you can easily create Database Users with Microsoft Access using the following two procedures, passing the Login Name to the CreateSQLAzureDBUser Function:

'Example usage: Call CreateSQLAzureDBUser("MyLoginName")
Public Function CreateSQLAzureDBUser(strLoginName As String) As Boolean
On Error GoTo ErrHandle
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    CreateSQLAzureDBUser = False 'Default Value
    strSQL = "CREATE USER " & strLoginName & " FOR LOGIN " & strLoginName
    'Create the Database User
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("")
    'Change obfuscatedFunctionName to the name of a Function
    'that Returns your SQL Azure Database Connection String
    qdf.Connect = obfuscatedFunctionName
    qdf.SQL = strSQL
    qdf.ReturnsRecords = False
    qdf.Execute dbFailOnError
    'If no errors the Database User was Created
    CreateSQLAzureDBUser = True

    '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 CreateSQLAzureDBUser"
    Resume ExitHere

    End Function

The Database User must be created in the Database in which it is to be used and not in the master Database. You can do this by changing MySQLAzureDatabaseName in the obfuscatedFunctionName Function to the name of the database in which you want to create the users.

    '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

For better security you can keep the Login Name, Password, and User Name hidden in the code without exposing it to the Access user.

We now have the ability to create Database Users but we still need to Grant Permissions before our Access Database can use this User Account to access any data in SQL Azure. We plan to show how you can do that in the articles to come.

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.

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

Login Status: Not logged in.
Login      Free Registration

  Call Us for a Free Consultation




Site Navigation

Skip Navigation Links.
Return to Top