Amazon RDS SQL Server Change Database Owner

2
votes

I know I can't make my user in RDS a sysadmin...but can I at least change the database owner from rdsa to my own user? If I use SSMS and go to change the Owner in the database properties, I get the message:

The proposed new database owner is already a user or aliased in the database

Why do I want to do this instead of just adding my user to the db_owner role?

I have indexed views I want to create, and when trying to do so, I get the error

Index cannot be created on view '...' because the underlying object '...' has a different owner.

I know that I can manually use ALTER AUTHORIZATION on the underlying objects to allow this, as described here: https://dba.stackexchange.com/questions/9436/sql-server-2008-cannot-create-index-on-indexed-view. But I really don't want to do that for every single object referenced by every one of my indexed views.

I'm certainly open to suggestions on alternative approaches.

Thanks.

1
sql-serveramazon-rds

1 Answers

3
votes

Index cannot be created on view '...' because the underlying object '...' has a different owner.

This is the real problem you're trying to solve, right? Changing the database owner (which would be done by running ALTER AUTHORIZATION ON DATABASE::<dbname> TO <loginname>) is just a red herring.

To solve this problem is unlikely you need change th owner of the underlying object, is far more likely you want to change the owner of the view, and probably correct solution is to create the view in the same schema as the underlying table:

create view schema.indexedView
with schemabinding
as
select ... 
from schema.table
where ...

Note that I say 'likely' and 'probably', this is not excuse I'm uncertain of the solution. Is because only you can tell us who the owner should be, based on the specific business rules, audit settings and access permissions of your site. Also, the fact that everything is database contained (the table, the indexed view, the schema containing the table and the owner of the schema) it means that the fact that this is an RDS instance is completely irelevant.