HowTo: create users and logins on an SQL server via SSDT
This is the article I was searching for when I was creating my database solutions as code, maybe it is out there written by someone else. Ever since I’m am developing databases and server-side processes for SQL server, I wanted to have all my definitions and configurations in a repository. The goal is to have the complete SQL server solution as code. Ultimately to deploy it with literally one click.
For SQL Server I use the SSDT from Microsoft, really an impressive solution to the aforementioned task. One thing I was always struggling with, security. Especially, User and Login creation.
It is pretty easy. Let’s assume we want to create a Schema, a login, a user, and grant some permissions on the schema to the SQL server login.
In this example, we use Azure Data Tool (you need the SQL Database Projects Extention installed) to work on our database. Either create a new database project.
First, we add the new schema to our project:
Though we create multiple schemas in one script in the example, I feel like it is good practice to define each schema in its own script.
We add another SQL script, where we define our new user and the necessary login:
This will build in the SSDT Solution, but when we deploy it, we’ll get an error:
The trick here is to add a reference to the master database as a system database. In Azure Data Studio right-click Database References
And add a Reference to the master database on the same server:
Now you can build the database solution and deploy it.
Do you create your SQL server projects using SSDT and Azure Data Studio? Which Solutions do you use? Do you want to know more about SQL Server and other database development projects?
If this HowTo was helpful, please consider:
