Is it possible to add a user with login to readonly database?

Question

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?

Have you tried creating the login at the server level and mapping that to the read-only db? — SS_DBA
You should create the user in your primary database and then it will be available on replicas. — Denis Rubashkin
@DenisRubashkin Yes, I've already created a user in the primary DB and it's copied to replica automatically but it seems like I cannot login to replica under its credentials — Roman Koliada
That means you already have the user in your database and you don't have to create it. I can suppose that you created the logins for that user on your servers separately, that's why they have different SIDs. Recreate the login on a replica with a correct SID from the primary server. — Denis Rubashkin

Roman Koliada Roman Koliada · Accepted Answer · 2019-04-25T14:46:08

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).

Is it possible to add a user with login to readonly database?

1 Answers