Azure SQL Creating a User

The following steps detail how to use SQL Management Studio to add a user to an Azure SQL database, give that user permissions and additional steps for the mirror server if you are Geo replicating the database

This guide assumes you have already created you database and got connected using SSMS.

Step 1 – Create the user on the server

use master
CREATE Login [myNewUser] WITH PASSWORD = 'TheUsersPassword'
Azure SQL create user

Step 2 – Create the user on your database

use [myDatabase]
CREATE USER [myNewUser] FROM LOGIN [myNewUser] 
Azure SQL create user on database

Step 3 – Give your new user the required permissions to your database

The most common roles are db_datareader, db_datawriter, db_owner. To give our new user db_owner permissions we would use the following 

use [myDatabase]
ALTER ROLE db_owner ADD MEMBER [myNewUser]; 
Azure SQL give a user db_owner

Additional steps for Geo-replicated databases

If you are using the geo-replication feature to replicate your database to another region you will need to create the same use on your secondary server(s) with the same SID using the following steps.

Getting the SID of the user from your primary server

use master
select sid from sys.sql_logins where name = 'myNewUser'

Creating the user on your secondary server(s)

Replace the SID with what we got from the previous select query on the primary server. Make sure the username and password also match you primary server

use master
create login [myNewUser] with password = 'TheUsersPassword', sid=0x010600000000006400000000000000001C98F52B95D9C84BBBA8578FACE37C3E

On the secondary server(s) we don’t need to add the user to the database or give it permission, that will all come over as part of the replication. We just need to create the user at the server level using the above.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.