Azure SQL, create users and assign permissions (Manual)

This simple manual has been created to create an user in Azure SQL and assign appropriate permissions. First connect to your SQL server. Either use and AAD admin account or the SQL Admin account.

Once connected, open a New Query window and run the following command on the Master database to create the user on the server in the Master database:

 CREATE LOGIN "<Username>" WITH password='<strong-password>';

Now open again a New Query window, and select the database where you want to provision permissions to the just created user. Make sure to match the Username from the command above.

CREATE USER "<Username>";

The last step is to assign the desired role to the user. Change the value of the role, and match again the Username.

EXEC sp_addrolemember 'db_datawriter', '<Username';

This should do the trick. Let me know if you have any problems or need help.

4 Replies to “Azure SQL, create users and assign permissions (Manual)”

  1. I’ve been struggling trying to get a ‘user’ into SqlAzure. I followed the above and still hit the same problem – when you try to log in with the new user account in SSMS it says “The server principal is not able to access the database “master” under the current security context. Cannot open user default database. Login failed. Error 916.
    Nomatter how much I try to google an answer, I have been unsuccessful. (I’ve unchecked collation in SSMS).
    Any help would be most welcome.

  2. Hi Paul,

    In SSMS go to the tab Connection Properties, and change the Connect to database to the database that you want to manage.
    That should solve your problem!


Leave a Reply

Your email address will not be published. Required fields are marked *