How to create an user and logins in Azure SQL PaaS DB
Here is the requirement of one of my customer who would like to add SQL server logins to the server that have access to different databases within the server
To be more specific, the requirement was to create a separate login for each user and they would like to give an access to specific database.
Proposed two options here-
Option 1 : Create a login and provide access to specific Database.
Option 2 : If they want to provide access to specific DB, we can simply create a user directly on the database, in this case we can only provide access to one DB or if they prefer to give access to multiple databases login is the best option.
Here is the resolution provided, we created a container user or login to provide access to the users
/*1: Create SQL Login on master database (connect with admin account to master database)*/
CREATE LOGIN loginname WITH PASSWORD = ‘<strong_password>’;
/*2: Create SQL user on the master database (this is necessary for login attempt to the <default> database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)*/
CREATE USER username FROM LOGIN loginname;
/*3: Create SQL User on the user database (connect with admin account to user database)*/
CREATE USER username FROM LOGIN loginname;
Note : You may need to create this user on all user databases if you are planning to provide access on more than one database.
/*4. Grant permissions to the user by assign him to a database role*/
ALTER ROLE db_datareader ADD MEMBER loginname;
Data reader is one kind of role to give only read access to the user, please find below URL for all list of database roles available in SQL Azure. You can modify the script based on your requirement.
Microsoft reference document for more details.
Above process is to create login and then map users to that login, alternatively we can also create a contained user by running below scripts
/*1: Create SQL USER on specific database (connect with admin account)*/
CREATE user username WITH PASSWORD = ‘<strong_password>’;
/*4. Grant permissions to the user by assigning a database role*/
ALTER ROLE db_datareader ADD MEMBER username;
Hope this help to create an user and logins in Azure SQL PaaS Database.
Happy Learning :)