How to undo assigning ownership of db_datareader/db_datawriter schema?

Question

i meant to assign an SQL Server login to the

• db_datareader
• db_datawriter

database roles. But if a moment of sore tummy and tiredness, i accidentally give that user schema ownership of them instead:

Ignoring for the moment what it can conceptually mean for a user to "own" those two built-in schemas. And ignoring for the moment if it is even a problem if a user owns those two schemas (e.g. if i want to delete the users will the built-in schemas go with it).

My question is: How do i undo it?

---

i randomly hit keys on my keyboard, and it came out:

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [db_datareader]F5

But that didn't do anything; so now it's time to consult the experts.

Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition

Solution 1

alter authorization on schema::[db_datareader] to [dbo]
alter authorization on schema::[db_datareader] to [db_datareader]

alter authorization on schema::[db_datawriter] to [dbo]
alter authorization on schema::[db_datawriter] to [db_datawriter]

Solution 2

do the following it will make your work easy: open SQL Server as SA account click on create new query and past blow queries and click on execute! done.

alter authorization on schema::[db_datareader] to [dbo]
alter authorization on schema::[db_datareader] to [db_datareader]
alter authorization on schema::[db_datawriter] to [dbo]
alter authorization on schema::[db_datawriter] to [db_datawriter]
alter authorization on schema::[db_securityadmin] to [dbo]
alter authorization on schema::[db_securityadmin] to [db_securityadmin]
alter authorization on schema::[db_accessadmin] to [dbo]
alter authorization on schema::[db_accessadmin] to [db_accessadmin]
alter authorization on schema::[db_backupoperator] to [dbo]
alter authorization on schema::[db_backupoperator] to [db_backupoperator]
alter authorization on schema::[db_ddladmin] to [dbo]
alter authorization on schema::[db_ddladmin] to [db_ddladmin]
alter authorization on schema::[db_owner] to [dbo]
alter authorization on schema::[db_owner] to [db_owner]

Comments

[Ben Thul]

Regarding your edit, only one of each pair should be necessary. alter authorization changes the ownership of the securable (in this case, a schema) to the given principal. So, either you want dbo to own the schemas (as they are in the databases that I checked on my side) or you want them to own themselves. The choice is mutually exclusive (as far as I know).