Home / server / Questions / 516443
Accepted
Ian Boyd
Asked: 2013-06-18 11:11:36 +0800 CST

How to undo assigning ownership of db_datareader/db_datawriter schema?

  • 772

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:

enter image description here

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

sql-server

2 Answers

  1. Best Answer
    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]
    • 16

  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]
    • 1