Creating Azure SQL Database Logins and Users - Article on SQLNetHub

Creating Azure SQL Database Logins and Users

by

This quick tip, talks about creating Azure SQL Database logins and users after you have performed an initial setup of your Azure SQL Database server and defined the admin user.

So, if for example, you would like to create a new SQL login/user with db_owner permissions on a database, you can do this by connecting to the Azure SQL Database server via SQL Server Management Studio or Azure Data Studio and create the login and user with T-SQL statements.

For example if you would like to create a new SQL login on Azure SQL Database (i.e. name it “testLogin1“) and you would like to make this login a db_owner on database “TestDB1“, then the process would be the below:

Step 1: Connect as Admin to your Azure SQL Database Server

Connect to your Azure SQL Database server as an admin via SQL Server Management Studio or Azure Data Studio from on premises.

 

Step 2: Switch to Database “master”

Switch the query window’s context to database “master” by switching to the “master” database from the database selection box in SQL Server Management Studio or Azure Data Studio.

 

Step 3: Create the Server Login

Create the login with the below T-SQL statement (you can change of course the login name and password accordingly in order to use a strong, secure password):

-- create SQL login in master database
CREATE LOGIN testLogin1
WITH PASSWORD = '<Strong_Password_Goes_Here>';

 

Step 4: Create the Database User

In another query window in SSMS or Azure Data Studio which is connected to your Azure SQL Database Server, select the database for which you want to create the user (i.e. in this example “TestDB1”) and grant permissions (i.e. db_owner) and run the below T-SQL statements (you can change of course the below T-SQL in order to grant the permissions/database roles you like):

-- add database user for login testLogin1
CREATE USER [testLogin1]
FROM LOGIN [testLogin1]
WITH DEFAULT_SCHEMA=dbo;

 

Step 5: Grant Database Permissions to the Login/User

The last step, is to grant the login with the desired database permissions. In the below example, I will be granting db_owner permissions to testLogin1:

-- add user to database role(s) (i.e. db_owner)
ALTER ROLE db_owner ADD MEMBER [testLogin1];

 

That’s it! Now you can use this new login to connect to the database based on the permissions you granted.

 

Learn more about Azure SQL Database: Enroll to our Online Course!

Enroll to our online course on Udemy titled “Introduction to Azure SQL Database for Beginners” and get lifetime access to high-quality lessons and hands-on guides about all aspects of Azure SQL Database.

Introduction to Azure SQL Database (Online Course - Lifetime Access)
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

Watch More Video Tutorials on Azure:

 

 

Featured Online Courses

 

Read Also:

 

Check our latest software releases!

Easily generate SQL code snippets with Snippets Generator!

Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (15 votes, average: 4.47 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



Views: 25,152