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.Enroll Now with Discount!
Watch More Video Tutorials on Azure:
Featured Online Courses
- Essential SQL Server Administration Tips (new)
- The Philosophy and Fundamentals of Computer Programming (new)
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Ultimate Beginners Guide)
- SQL Server Fundamentals (SQL Database for Beginners)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- Learn Azure SQL Database: Creating your First Database
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- Built-In JSON Support in SQL Server and Azure SQL Database
- Learn Azure Data Lake Analytics by Example
- Azure Cosmos DB: Learn by Example
- Azure Resource Cost Estimation and Management
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
© 2018 SQLNetHub
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).