1

Topic: ALTER AUTHORIZATION ON DATABASE:: MyDB TO sa and AlwaysOn

Good afternoon!
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
Problem:
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?

2

Re: ALTER AUTHORIZATION ON DATABASE:: MyDB TO sa and AlwaysOn

1. At what here routing? From it the basis on secondary  does not become accessible for alter.
2. The basis on secondary  will be in restoring and any alter you to it do not make.
3. failover make and .
If do not want problems in the future, do  on secondary  under login which should be the owner of basis. Though trustworthy all the same flies.

Oblom wrote:

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.

If they are directed there through ApplicationIntent=ReadOnly all of them go the automatic machine (well, almost) on primary if the secondary are inaccessible. If it is made somehow in another way, .

3

Re: ALTER AUTHORIZATION ON DATABASE:: MyDB TO sa and AlwaysOn

Sergey Alekseevich wrote:

1. At what here routing? From it the basis on secondary  does not become accessible for alter.
2. The basis on secondary  will be in restoring and any alter you to it do not make.
3. failover make and .
If do not want problems in the future, do  on secondary  under login which should be the owner of basis. Though trustworthy all the same flies.

Thanks for the torn answer, mean we will move.

Sergey Alekseevich wrote:

it is passed...
If they are directed there through ApplicationIntent=ReadOnly all of them go the automatic machine (well, almost) on primary if the secondary are inaccessible. If it is made somehow in another way, .

And it is good news, it is made yes through ApplicationIntent=ReadOnly