12

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

Ian Boyd
  • 5,131
  • 14
  • 57
  • 79

2 Answers2

16
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]
Ian Boyd
  • 5,131
  • 14
  • 57
  • 79
Ben Thul
  • 2,969
  • 16
  • 23
  • 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). – Ben Thul Jun 20 '13 at 17:18
1

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]
MJ X
  • 111
  • 3