Google Cloud SQL - Unable to change DB owner on restored database from .BAK file-CodePudding

I have restored a SQL Server 2019 database from a .BAK file to a Google Cloud SQL instance.

I can connect to the instance using Visual Studio SQL Connection. I issue the following command to check the database owner, which should be: mydb_adm

USE master;
SELECT suser_sname(owner_sid) AS 'DB Owner' FROM sys.databases WHERE name = 'mydb';

DB Owner
--------
sqlserver

The above is expected, as the restore was done while using the sqlserver account which is the default user created when the SQL instance is provisioned by Google Cloud (according to the docs).

So, I need to change the DB owner; I execute the following command:

USE mydb
EXEC sp_changedbowner 'mydb_adm'

The system displays the following error message:

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

The same message is displayed for:

ALTER AUTHORIZATION ON DATABASE::mydb TO mydb_adm;

However, the "mydb_adm" principal DOES exist, i.e.:

SELECT name, sid FROM sys.server_principals WHERE name = 'mydb_adm';

name        sid
----        ---
mydb_adm    0xD81398C7DB0D724BB2738A2EC59BB554

.. so it must be a permission problem with the sqlserver account. When I query the DB, it appears the "sqlserver" user does NOT have ALTER permissions, i.e.:

UserName    Permission Type   Permission State
--------    ---------------   ----------------
sqlserver   ALTER             DENY

... So how can I change the database owner or issue any ALTER commands using the "sqlserver" account? (There seems to be no way to grant the ALTER permission to the sqlserver user).

Any help / advice would be appreciated.

CodePudding user response：

Thank-you to @DanGuzman for providing a "work-around", i.e.: while connected to the SQL instance using the "sqlserver" user, the following commands were used:

USE mydb;
CREATE USER mydb_adm;
ALTER ROLE db_owner ADD member mydb_adm;

After some additional digging, I also found the following in the Google Cloud docs at https://cloud.google.com/sql/docs/sqlserver/users, which states:

Cloud SQL for SQL Server is a managed service, so it restricts access to certain system stored procedures and tables that require advanced privileges. In Cloud SQL, you cannot create or have access to users with superuser permissions.

Note: The sysadmin role is not supported. Therefore, you cannot run system stored procedures that require the sysadmin role. As one of the many examples, you cannot run the sp_OADestroy stored procedure because it requires the sysadmin role.

As well as the following from the SQL Server docs at https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15#remarks, which state:

Requirements for the person executing the ALTER AUTHORIZATION statement: If you are not a member of the sysadmin fixed server role, you must have at least TAKE OWNERSHIP permission on the database, and must have IMPERSONATE permission on the new owner login.

hence, commands such as EXEC sp_changedbowner ... or ALTER AUTHORIZATION ON DATABASE:: ... will raise the error (Msg 15151, ... you do not have permission.)

Hope that helps anyone else that may run into this type of issue.