Sample Header Ad - 728x90

Change database owner of Azure SQL Database

2 votes
0 answers
3300 views
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](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15#alter-authorization-for-databases) , 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.
Asked by sidesw1pe (21 rep)
Jun 25, 2021, 01:25 AM
Last activity: Jun 25, 2021, 03:55 AM