Sql-server – Unable to change database owner to a user that is already mapped as user

permissionssql serversql-server-2008-r2

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?

Best Answer

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.

Related Question