how do I change the owner of an azure sql database that has been restored and ensure db_owner permissions

Question

I have restored an Azure SQL Database, using the Azure Portal. The database owner has changed to the sqladmin user, which was used to restore the database. I have a login, UserA, who I want to make as the database owner, which I have done so, by using the ALTER AUTHORIZATION ON DATABASE command. When I check the properties of the database, I can see the dbowner has changed to UserA. On the database I have dbo, which is the default user which db_owner role. I can see that the SID of the login UserA matches that of the SID of dbo. However, after logging in using UserA, when I attempt to select from a table, I get a permissions issue, however, after a couple of hours, it seems to work. My question is I am I following the correct process and is there a delay is the ALTER AUTHORIZATION ON DATABASE taking affect?

Answer 1

@Mandeep Dhillon
You have followed the correct process to change the database owner using the ALTER AUTHORIZATION ON DATABASE command.

Changing the owner of a database

The following example change the owner of the Parts database to the login MichikoOsada.

ALTER AUTHORIZATION ON DATABASE::Parts TO MichikoOsada;

However, there might be a delay in the ALTER AUTHORIZATION ON DATABASE command taking effect. This is because the new owner’s SID must be propagated to all objects in the database, which can take some time depending on the size of the database and the number of objects it contains. Please let us know if you continue to experience the same. Regards, Oury