Wednesday, March 18, 2020

Create SQL Login and SQL User on your Azure SQL DB


/*1: Create SQL Login on master database (connect with admin account to master database)*/
CREATE LOGIN MyLogin WITH PASSWORD = '';


/*2: Create SQL user on the master database (this is necessary for login attempt to the database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*3: Create SQL User on the user database (connect with admin account to user database)*/
CREATE USER MyUser FROM LOGIN MyLogin;


/*4. Grant permissions to the user by assign him to a database role*/
ALTER ROLE db_datareader ADD MEMBER MyUser;


/*Login with the newly created login:

With SSMS  use login name and password to connect directly to user database \ master \

When using contained database used, you must set the here to be able to connect.
*/