How do I recreate a user/login on a SQL Azure Active Geo-Replication Secondary

Question

I have a Premium P2 SQL Azure Database for my Production App, and for security reasons I've created DB Specific Schemas/Views/Roles and a specific DB User account for reading the Data from SSRS Queries.

Lets just call the server PRIMARY and my database MyApp

The setup script for this is below.

-- On Primary Master
CREATE LOGIN ssrsuser WITH password='******'

-- On Primary MyApp
CREATE USER ssrsuser FOR LOGIN ssrsuser 
CREATE ROLE [ssrsreader] AUTHORIZATION [db_owner]
GRANT SELECT ON SCHEMA :: [App] TO [ssrsreader]
GRANT SELECT ON SCHEMA :: [Reports] TO [ssrsreader]
EXEC sp_addrolemember 'ssrsreader', 'ssrsuser'

So our users were putting a LOT of load on the Prod DB and we decided it was time to move the reporting functions off to a secondary sync'd slave database.

Since we're using the SQL Azure Premium Tier, we can enable Active Geo-Replication with a read-only secondary copy. In fact MS even say that it's suitable for read-only workloads such as reporting.

So I've setup the SECONDARY server, enabled the seeding, it's now complete and I can access the readonly copy using the SECONDARY admin user and password.

But the SECONDARY server doesn't have a login for ssrsuser and I while I can create one in SECONDARY.master, I can't DROP RECREATE the user since the SECONDARY.MyApp database is in readonly mode.

Is there any otherway to get around this. I really don't want to have to put the SECONDARY server admin user & password into SSRS connection strings.

Answer 1

There is no need to regenerate SID for ssruser in the user database. It is already there as a result of replication. All you need to do is associate that SID with a LOGIN in the master in the secondary server. This article provides the details. https://azure.microsoft.com/en-us/documentation/articles/sql-database-geo-replication-security-config/

I hope this helps.