Previously we provisioned three Azure SQL Servers, one Primary with two Readable Secondaries.
Next we need to create a new user with db_datareader permissions in this example, perhaps for a reporting application.
First we need to add an allow rule in the firewall on each server for our IP, in this example 220.127.116.11 so we can access the databases externally.
Now we can use SQL Server Management Studio (SSMS) to access each SQL Azure database directly.
Setup our Primary.
Connect to your database using SSMS and on the connection properties, second, tab enter master in the connect to database option.
Then run the CREATE LOGIN command;
CREATE LOGIN MyUser WITH PASSWORD = ‘P@ssword1’;
SELECT [sid] FROM [sys].[sql_logins] WHERE name = ‘MyUser’
Copy the output from the above query, we’ll need it soon when creating the server login on each secondary.
Then change context to the database by right clicking on your database and selecting New Query and then run the following commands, which add the user to the database and setup the datareader role; Connect to a secondary with admin credentials and again in the context of the master database, again using the connection properties tab run the CREATE LOGIN command below, note the WITH SID.
CREATE LOGIN MyUser WITH PASSWORD = ‘P@ssword1’, SID = 0x0106000FF0000064000EA000000000009C71D9C12F932B47976883220239FC4E
The logic here is that the database user MyDatabaseUser is copied over with the database synchronisation but the server level login MyUser is not. For the new MyUser server level login on the secondary to map to MyDatabaseUser the SID must match.