Creating Azure SQL Database Logins and Users

Creating Azure SQL Database Logins and Users

Creating Azure SQL Database Logins and Users

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: Take the Online Course!

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

Introduction to Azure SQL Database (Online Course - Lifetime Access)

Online Course: Introduction to Azure SQL Database

Enroll Now with Discount!

 

Watch More Video Tutorials on Azure:

 

 

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 (1 votes, average: 5.00 out of 5)

Loading...

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

© 2018 SQLNetHub

Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com.