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:
- 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