HowTo: create users and logins on an SQL server via SSDT

Otrek Wilke
2 min readDec 22, 2020

--

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:

Can’t deploy a user for a login where we do not have permissons?

The trick here is to add a reference to the master database as a system database. In Azure Data Studio right-click Database References

Add Database Reference menu

And add a Reference to the master database on the same server:

Configure and add the reference

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:

--

--

Otrek Wilke
Otrek Wilke

Written by Otrek Wilke

Data Engineering made easy. Writing about things learned in data engineering, data analytics, and agile product development.

No responses yet