I want to change the database owner of an Azure SQL Database. I can do this via the server administration user. However I want to do it using a different user. Why? Because I have a process which copies a database from one server to another using a specific user account that is not the server administration user, and I need this user to be able to set the db owner after the copy completes.
Here's the deal.
I've an Azure SQL Database database1 owned by user1. I want to change the database owner to user2. Both users are SQL authentication logins.
Running the below query from the master database, I can see the user who owns database1 is user1 with SID 0x01060000000000640000000000000000F67524E3236132449A7483D4456F051E.
SELECT d.name, d.owner_sid, sl.name
FROM sys.databases AS d
JOIN sys.sql_logins AS sl
ON d.owner_sid = sl.sid;
I login to database1 with user1. Running the below query, I can see my SID is 0x01060000000000640000000000000000F67524E3236132449A7483D4456F051E
SELECT USER_SID()
Logged in to database1 as user1, I run the query below, from the context of database1 (target database), to update the database owner, and I get the error "User does not have permission to perform this action.".
ALTER AUTHORIZATION ON DATABASE::database1 TO [user2];
According to the Microsoft documentation, the conditions for changing an Azure SQL Database owner are:
- The new owner principal can be a SQL Server authentication login.
- The person executing the ALTER AUTHORIZATION statement must be connected to the target database.
- The person changing the owner of a database can be the current owner of the database.
So I am unsure what I've missed, but I would like to know why I am unable to change the database owner.
