4

I ran into this problem. Trying to change a database owner to acct1; however, I get the following error message when doing so:

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

Now I know one solution is to drop the acct1 user and then change the database owner, but I can't do that because all the tables in this database are owned by the acct1 schema.

How can I get around this?

3

You can easily change the schema ownership to dbo, then you should be able to drop the user, and add them back using ALTER AUTHORIZATION:

ALTER AUTHORIZATION ON SCHEMA::[schema_they_own] TO dbo;
DROP USER [user_name];
ALTER AUTHORIZATION ON DATABASE::[database_name] TO login_name;

Now, they will be the dbo user in the database (and will have automatic ownership of the schema).

You may need to change more than one schema; you can generate a script to do this using dynamic SQL.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER AUTHORIZATION ON SCHEMA::'
  + QUOTENAME(s.name) + N' TO dbo;
'
FROM sys.schemas AS s
INNER JOIN sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.name = N'database user name';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

If you are happy with the command generated, you can copy and paste the output and run it, or you can uncomment the EXEC and run it again.

  • But I think in order for the application to work, the tables need to be under the schema owned by the the user and not the dbo user. So would I be able to run "ALTER AUTHORIZATION ON SCHEMA::dbo TO login_name;" after the database owner has been changed to login_name? – Chinesinho Jul 2 '15 at 14:17
  • The application isn't going to care who has authorization on the schema. The tables will still belong to the same schema (you are referencing the schema name explicitly in your app, right?). You can't change the schema to be owned by login_name because login_name doesn't exist in the database. Since you mapped login_name to dbo (by making them the database owner), the user name is now dbo. Perhaps you should ask yourself why you want this user to be the database owner. – Aaron Bertrand Jul 2 '15 at 14:24
  • Once I run ALTER AUTHORIZATION ON SCHEMA::acct1 TO dbo;, shouldn't all the tables become dbo.table_name instead of the original acct1.table_name? – Chinesinho Jul 2 '15 at 14:31
  • @Chinesinho NO. Did you try it? This isn't renaming acct1 to dbo. It is merely changing its ownership. Ownership of a schema is not the same thing as the schema container of a table - it is just confusing in this case because dbo has multiple purposes. – Aaron Bertrand Jul 2 '15 at 14:33
  • I did and when I try to run "DROP USER [user_name];" after "ALTER AUTHORIZATION ON SCHEMA::acct1 TO dbo;", I get the following: "The database principal owns a schema in the database, and cannot be dropped." – Chinesinho Jul 2 '15 at 14:37

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.