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';

If you want to view the current permissions on Azure SQL database you can run the following command.

SELECT roles.principal_id AS RolePrincipalID
, roles.name AS RolePrincipalName
, database_role_members.member_principal_id AS MemberPrincipalID
, members.name AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members
JOIN sys.database_principals AS roles
ON database_role_members.role_principal_id = roles.principal_id
JOIN sys.database_principals AS members
ON database_role_members.member_principal_id = members.principal_id;

If you want to add a user from Azure AD, you can use the following command:

CREATE USER [cor@2azure.com] FROM EXTERNAL PROVIDER;