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.
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.
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.
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?
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.
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:
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.