I have a sql azure database which is geo-replicated. Now I want to create a user with login and grant him a permission to read data from some table in the readonly replica. But it seems like it's not possible.
When I'm trying to create a user for login I get a error message about readonly database:
CREATE USER [user_name] FOR LOGIN [login_name]
Failed to update database "test-master" because the database is read-only.
When I'm trying to make a database read-write it tells me that it's not possible because of geo-replication:
ALTER DATABASE [test-master] SET READ_WRITE
ODBC error: State: 42000: Error: 1468 Message:'[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The operation cannot be performed on database "test-master" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.'. Msg 5069, Level 16, State 3, Line 5 ALTER DATABASE statement failed.
So is it possible at all?
UPDATE:
It's seems like login info is not replicated.
Script User as CREATE TO in Master db:
CREATE USER [user_name] FOR LOGIN [login_name] WITH DEFAULT_SCHEMA=[dbo]
Script User as CREATE TO in Replica db:
CREATE USER [user_name] WITH DEFAULT_SCHEMA=[dbo]
Does it mean that it's not possible to add a new login to the replica db?
I found a solution here: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/05/21/tips-tricks-what-you-need-to-do-to-creat-sql-login-to-authenticate-to-azure-sql-dbs-members-in-auto-failover-group/
It's possible to use Forced Failover feature which swap databases and hence make my replica writable so I can create a user and grant permissions(of course during this change previous master db becomes readonly).