Changed Owned Schema For User In SQL Server

I accidentally set “Owned Schemas” for a new user in SQL Server 2005 to db_backupoperator and db_owner. Not what I wanted, and then discovered I couldn’t change them back using SQL Management Studio. Oh, well, the following http://blog.sqlauthority.com/2008/04/05/sql-server-2005-transferring-ownership-of-a-schema-to-a-user/ helped out.

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO UserName;
GO

For my case, this fixes it:

ALTER AUTHORIZATION ON SCHEMA::db_backupoperator TO db_backupoperator;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner;
GO

Phew! Fixed it. I then added the new user to the db_backupoperator and db_owner roles instead in Role Membership which is what I should have originally done.

Download PDF
This entry was posted in Snippets, SQL Server and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *