How to execute sp_changedbowner via AWS RDS SQL Server master user？

Question

Recently, I have created an AWS RDS SQL Server database (SQL Server 2017 Express Edition, v14.00.3281.6.v1 using free tier templates). Then I can use the master user name and password to login the database server, and I can create database/login, but I cannot execute

EXEC sp_changedbowner 'testLogin'

to change the dba to the new-created login testLogin, I get an error

SQL Error [15151] [S0001]: Cannot find the principal 'testlogin', because it does not exist or you do not have permission.

The following is the whole SQL script I run. It seems that the master user doesn't have enough permission? Could anyone tell me how to fix it or how to make the script run successfully. BTW, the same script runs successfully in a traditional SQL Server database.

Thanks very much.

SET IMPLICIT_TRANSACTIONS OFF

USE master;

-- Get the SQL Server data path
DECLARE @data_path nvarchar(256)
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1)
DECLARE @model_data_size_kb int
SET @model_data_size_kb = (SELECT SUM(size) * 8
                  FROM master.sys.master_files
                  WHERE database_id = 3 and [type] = 0)
IF @model_data_size_kb < (250 * 1024)
    SET @model_data_size_kb = 250 * 1024
DECLARE @model_log_size_kb int
SET @model_log_size_kb = (SELECT SUM(size) * 8
                  FROM master.sys.master_files
                  WHERE database_id = 3 and [type] = 1)
IF @model_log_size_kb < (50 * 1024)
    SET @model_log_size_kb = 50 * 1024
DECLARE @schema_data_size_str nvarchar(12)
DECLARE @schema_log_size_str nvarchar(12)
SET @schema_data_size_str = CAST(@model_data_size_kb AS nvarchar(12))
SET @schema_log_size_str = CAST(@model_log_size_kb AS nvarchar(12))
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE testdb
ON
( NAME = testdb_dat1,
    FILENAME = '''+ @data_path + 'testdb.mdf'',
    SIZE = ' + @schema_data_size_str + 'KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 25MB )
LOG ON
( NAME = testdb_log1,
    FILENAME = '''+ @data_path + 'testdb.ldf'',
    SIZE = ' + @schema_log_size_str + 'KB,
    MAXSIZE = 10GB,
    FILEGROWTH = 5MB )
COLLATE SQL_Latin1_General_CP1_CS_AS'
);
GO

ALTER DATABASE testdb SET READ_COMMITTED_SNAPSHOT ON;

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE type = 'S' and name = N'testlogin')
BEGIN
    CREATE LOGIN testlogin WITH PASSWORD='testlogin', DEFAULT_DATABASE=testdb, CHECK_POLICY = OFF   
END


USE testdb;

EXEC sp_changedbowner 'testlogin';  -- this statement reports exception

Jason Tian Jason Tian · Accepted Answer · 2020-05-26T08:33:00

After research, I found that AWS RDS SqlServer doesn't support add more permission to master user, which means that it can not execute "sp_changedbowner" since it require "Take Ownship" permission on the database and requires the new owner has "impersonate" permission on the login or "control server" permission on the server. click this for more detail about sp_changedbowner

That is we cannot change db owner to others in RDS SqlServer, it is always a built-in "rdsa" user, but we can use ALTER ROLE db_owner ADD MEMBER testlogin to add user to the db_owner role, so that it can perform any database actions.

So I changed my script to the following and works well:

-- The above is the same

USE testdb;

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE type = 'S' and name = N'testlogin')
BEGIN
    CREATE LOGIN testlogin WITH PASSWORD='testlogin', DEFAULT_DATABASE=testdb, CHECK_POLICY = OFF  
    CREATE USER testlogin FOR LOGIN testlogin 
END

ALTER ROLE db_owner ADD MEMBER testlogin

How to execute sp_changedbowner via AWS RDS SQL Server master user？

1 Answers