- SQL Server 2016
- SSMS v18.4
I want to create the same logins and give the same privileges on both node.
In primary node I have no problem about this operation.
But when I create the same login on secondary node and want to user mapping, it gives below error:
Create failed for login 'loginname'.
An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to update database "dbname" because the database is read-only. (Microsoft SQL Server, Error:3906)
But my database is not read-only.
I'm trying the give permission like this:
Node 2 (Secondary) -> Security -> Logins -> Right click on MyLogin-> Properties -> User Mapping -> Select database and choose dbo for default schema -> Select public and db_owner -> OK.
It gives this error all of the databases for secondary node.
How can I solve this problem without doing failover (failover can solve this because it does primary node to Node-2 but I don't want to failover)?
If it possible with using PowerShell, how can I do it on this operation on PowerShell for Node-2?