Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted
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]
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.