The following steps detail how to use SQL Management Studio to add a user to an Azure SQL 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'

Step 2 – Create the user on your database
use [myDatabase] CREATE USER [myNewUser] FROM LOGIN [myNewUser]

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

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.
Get 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.
Recent Comments