Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schemae is blue and unremovable. How can I remove the principal from these schemas?

share|improve this question

1 Answer

up vote 3 down vote accepted

Try the T-SQL to do this:

alter authorization
on schema::YourSchemaName
to dbo
go

drop user TheUserYouWantToDelete
go

You can't drop a principal that is a schema owner, so the ALTER AUTHORZATION changes the owned schema (I used YourSchemaName, but obviously substitute that with the owned schema in your database) to dbo (likewise, you can change ownership to whatever principal you need in your environment). That will allow you to drop the previously-schema-owning user (for example purposes I used TheUserYouWantToDelete, but that'll be the now non-owner that you want to drop).

share|improve this answer
Okay, so this worked. I'm a little confused as to why, however. Why do I need to authorize the schema to another schema? Maybe I need to review schemas... – rsteckly Jun 18 '12 at 18:10
@rsteckly Simplified, authorization = ownership. If you could delete the user that owns the schema, the schema wouldn't be valid, because the owner no longer exists. Imagine you could delete a customer who has placed an order. What does the CustomerID in the Orders table mean if there is no longer a Customer that it points to? Where should I ship the order? – Aaron Bertrand Jun 18 '12 at 18:20
Sorry, my example wasn't well explained. We're not changing authorization to the dbo schema, we're changing authorization to the dbo principal. That could have been any database principal. – Thomas Stringer Jun 18 '12 at 18:32

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.