Topic: ALTER AUTHORIZATION ON DATABASE:: MyDB TO sa and AlwaysOn
There is a following configuration:
Two server Node1 and Node2
SELECT @@ VERSION Microsoft SQL Server 2016 (SP1-CU6) (KB4037354) - 13.0.4457.0 (X64) Nov 8 2017 5:32:23 PM Copyright (c) Evaluation Edition MICROSOFT CORPORATION ENTERPRISE (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393:) (Hypervisor)
It is adjusted AlwaysOn: Node1 - primary, Node2 - secondary
Routing for usage Node2 as ReadOnly is adjusted
At request where the data undertakes from bases MyDB1 and MyDB2 we receive an error
"The security identifier of the owner of the database, written down in a database master, differs from the security identifier of the owner of the database which have been written down in a database" MyDB1 ". Eliminate this distinction, dropping the owner of a database" MyDB1 "by means of instruction ALTER AUTHORIZATION."
At attempt to carry out instruction ALTER AUTHORIZATION ON DATABASE:: MyDB1 TO sa it is received a logical error "it was not possible to update a database" Online "as it is intended only for reading."
Requests which use only the data from MyDB1 are fulfilled without problems.
Visible ways of a solution of a problem:
1. To delete routing, to launch on Node2 instruction ALTER AUTHORIZATION ON DATABASE:: MyDB1 TO sa, to return routing.
2. To remove basis MyDB1 from AlwaysOn, to launch on Node2 instruction ALTER AUTHORIZATION ON DATABASE:: MyDB1 TO sa, to return basis in AlwaysOn.
3. To move on Node2, to launch on Node2 instruction ALTER AUTHORIZATION ON DATABASE:: MyDB1 TO sa, to move reversely on Node1.
All decisions not so wished because it is all it is adjusted in fight, fighting requests already go on the second , to translate them to the first long and is troublesome.
Whether there are any other variants?
If is not present, what of my variants works with probability?
Someone can faced the same problem?