In the previous post, Azure SQL Pro Tip – Creating Login Account and User, we have briefly walked through how to create login accounts on Azure SQL Database through SSMS. Using SSMS is of course the very convenient way. However, as a DevOps engineer, I want to automate this process through PowerShell. In this post, we’re going to walk through how to achieve this goal.

Step #1: Create Azure SQL Database

First of all, we need an Azure SQL Database. It can be easily done by running an ARM template in PowerShell like:

We’re not going to dig it further, as this is beyond our topic. Now, we’ve got an Azure SQL Database.

Step #2: Create SQL Script for Login Account

In the previous post, we used the following SQL script:

Now, we’re going to automate this process by providing username and password as parameters to an SQL script. The main part of the script above is CREATE LOGIN ..., so we slightly modify it like:

Now the SQL script is ready.

Step #3: Create PowerShell Script for Login Account

We need to execute this in PowerShell. Look at the following PowerShell script:

Looks familiar? Yes, indeed. It’s basically the same as using ADO.NET in ASP.NET applications. Let’s run this PowerShell script. Woops! Something went wrong. We can’t run the SQL script. What’s happening?

Step #4: Update SQL Script for Login Account

CREATE LOGIN won’t take variables. In other words, the SQL script above will never work unless modified to take variables. In this case, we don’t want to but should use dynamic SQL, which is ugly. Therefore, let’s update the SQL script:

Then run the PowerShell script again and it will work. Please note that using dynamic SQL here wouldn’t be a big issue, as all those scripts are not exposed to public anyway.

Step #5: Update SQL Script for User Login

In a similar way, we need to create a user in the Azure SQL Database. This also requires dynamic SQL like:

This is to create a user with a db_owner role. In order for the user to have only limited permissions, use the following dynamic SQL script:

Step #6: Modify PowerShell Script for User Login

In order to run the SQL script right above, run the following PowerShell script:

So far, we have walked through how we can use PowerShell script to create login accounts and user logins on Azure SQL Database. With this approach, DevOps engineers will be easily able to create accounts on Azure SQL Database by running PowerShell script on their build server or deployment server.

Category:
Azure Infrastructure, Azure Platform, PowerShell
Tags:
, , ,

Join the conversation! 6 Comments

  1. I followed the steps through step 4, but it doesn’t work. I get this error:

    Exception calling “ExecuteNonQuery” with “0” argument(s): “The CREATE LOGIN statement must be the only statement in
    the batch.”
    At line:1 char:1

    Here’s what the command looks like:

    PS C:\Windows\system32> echo $command

    Connection : System.Data.SqlClient.SqlConnection
    NotificationAutoEnlist : True
    Notification :
    Transaction :
    CommandText : EXECUTE (‘CREATE LOGIN [‘ + @Username + ‘] WITH PASSWORD = ”’ + @Password + ””)
    ColumnEncryptionSetting : UseConnectionSetting
    CommandTimeout : 30
    CommandType : Text
    DesignTimeVisible : True
    Parameters : {@Username, @Password}
    UpdatedRowSource : Both
    Site :
    Container :

    Any thoughts on what I did wrong?

    • @Bryan Did you use double quotes or single quotes around the @Password parameter? If you used double quotes, they should be changed two single quotes for each double quote.

  2. I copied the exact code from step 4: EXECUTE (‘CREATE LOGIN [‘ + @Username + ‘] WITH PASSWORD = ”’ + @Password + ””)

    • The blog is changing them from single quotes to double quotes when I post.

    • Interesting. I used the exactly same SQL script from the step #4 and step #6, and worked as expected.

      Did you create the @Password parameter for the step #6? At step #4, we need both username and password but in the step #6 we only added @Username.

Comments are closed.