Creating users in Azure SQL DB

After creating a database under Microsoft Azure, the next step is usually to create a user who is to be authorized in the corresponding database.

Unfortunately, there is currently no direct option for this in the web interface of the Azure portal. Only the admin user for the DB server can be configured there. For security reasons, however, this should not be the user with which you later access the database from your own application.

With a local SQL Server installation, you would now open the SQL Server Management Studio at this point and create the user there conveniently via the interface. If you try to do this with an Azure SQL DB, you will be disappointed to learn that this interface is not available for Azure SQL databases.

Achieving the goal with T-SQL

Instead, you have to create the user with T-SQL. To accomplish this, connect with any SQL client, e.g. SQL Server Management Studio or Azure Data Studio with an administrative user to the corresponding database server with the database master and execute the following SQL statement to create a login:

CREATE LOGIN [MyUser] WITH PASSWORD=N'Test!987'
GO

MyUser and Test!987 are of course only placeholders for the actual username and the corresponding password at this point.

After the login is now known on the server, in the next step we have to create a user for the login in the target database (usually not master). By the way, we need such a user for every database the login should have access to.

So for this purpose, we first change to the corresponding database in our SQL client, e.g. via use [MyDatabase] and then execute the following statement.

CREATE USER [MyUser] FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]
GO

Finally, we have to give the user a suitable role according to the desired access rights.

For example, if the user should be able to read and write data, but not modify database structures, then the groups db_datareader and db_datawriter would be appropriate.

EXEC sp_addrolemember 'db_datareader', 'MyUser';
EXEC sp_addrolemember 'db_datawriter', 'MyUser';
GO

About the author

André Krämer

Dipl.-Inform. (FH) André Krämer, Msc. is a co-founder and the CEO of Quality Bytes GmbH. The multi-awarded Microsoft MVP has been active in software development since 1997 and specialized in Microsoft .NET development in 2002.

His expertise includes app development with .NET MAUI, Azure DevOps, document automation, and reporting with TX Text Control, as well as the analysis of memory leaks and performance issues. He is a regular author forlinkedIn Learning, where he already published more than thirty German video courses.

He is also a TX Text Control MVP and a Microsoft MVP. He has written a German book on Xamarin.Forms and another on .NET MAUI for the German publisher Hanser Verlag.