This is a cross-posting of Creating Accounts on Azure SQL Database through PowerShell Automation at Kloud.

In the previous post, Creating Login Account and User on Azure SQL, 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:

https://gist.github.com/justinyoo/226b02f30f3626adb4e151471aa1ae98

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:

https://gist.github.com/justinyoo/a0fcf112bda27c09ac54747c56e5fe11

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:

https://gist.github.com/justinyoo/71d22c8fa0e6da633858017a68d3dce5

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:

https://gist.github.com/justinyoo/071607ca0ab18f7eac56926a4167c3f4

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:

https://gist.github.com/justinyoo/7daf3cd112d003ee67e6a02cd8fee783

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:

https://gist.github.com/justinyoo/29d2c173d7c76838be44b329615bd4d2

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:

https://gist.github.com/justinyoo/8f910737a59677bf3f0f7a82787c318a

Step #6: Modify PowerShell Script for User Login

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

https://gist.github.com/justinyoo/ea1237b06e463e030f6cf072f2a6e878

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.