The following steps detail how to use SQL Management Studio to add a user to an Azure SQL database. How to 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 your database and got connected using SSMS.
- Step 1 – Create the user on the server
- Step 2 – Create the user in your database
- Step 3 – Give your new user the required permissions to your database
- Additional steps for Geo-replicated databases
Step 1 – Create the user on the server
use master
CREATE Login [myNewUser] WITH PASSWORD = 'TheUsersPassword'
Step 2 – Create the user in 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.
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 your 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, which will all come over as part of the replication. We just need to create the user at the server level using the above.
