2

Is db_owner permission sufficient to execute sp_changedbowner?

I'm looking to change the database owner of a RDS SQL instance. I'm the owner of the DB, and still couldn't execute sp_changedbowner as master user.

I would like to know what the minimum permissions are to execute sp_changedbowner or ALTER AUTHORIZATION commands.

  • Why do you need to change the database owner in RDS? – Dan Guzman Sep 30 '17 at 14:05
2

Community wiki answer:

From ALTER AUTHORIZATION (Transact-SQL):

Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role.

There may be additional considerations in the AWS RDS world.

Note that sp_changedbowner is deprecated in favour of ALTER AUTHORIZATION. The sp_changedbowner documentation says:

Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server.

1

I will show this with a demo. Most likely in your case you are missing CONTROL SERVER privilege.

A good reading about this topic will be:

Basic SQL Server Security concepts: ownership, CONTROL, TAKE OWNERSHIP by Laurentiu Cristofor

Running following code under a account with sysadmin privilege.

USE [master];
GO
--creating a new database
CREATE DATABASE [testpermission];
GO
--creating a SQL login
CREATE LOGIN [ownerone] WITH PASSWORD=N'ownerone', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
--making the new user owner of database
USE [master]
GO
ALTER AUTHORIZATION ON DATABASE::testpermission TO ownerone;  
GO
--checking the new owner
SELECT suser_sname(owner_sid) FROM sys.databases WHERE NAME = 'testpermission';
GO

You will see the new owner name is ownerone.

Now creating another login.

USE [master]
GO
CREATE LOGIN [newowner] WITH PASSWORD=N'newowner', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Now login as ownerone and execute following statement to give ownership to newowner.

ALTER AUTHORIZATION ON DATABASE::testpermission TO newowner; 

You get following error message:

Msg 15151, Level 16, State 1, Line 1 Cannot find the principal 'newowner', because it does not exist or you do not have permission.

Give ownerone login CONTROL SERVER privilege.

USE [master]
GO
GRANT CONTROL SERVER TO [ownerone];
GO

Repeat following and now you will be able to change ownership to newowner.

ALTER AUTHORIZATION ON DATABASE::testpermission TO newowner; 

Clean up code.

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'testpermission'
GO
USE [master]
GO
DROP DATABASE [testpermission]
GO
DROP LOGIN [newowner]
GO
DROP LOGIN [ownerone]
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.