August 30, 2011 at 9:09 am
This has me confused. I am dropping a user from the server. What I am doing first is going into the databases and looking to see if he is there.
If he is there, then here is what I have noticed:
--Sometimes I have to drop his schema first and then I can drop his user.
--Sometimes he has no schema and I can just drop him.
This does not make sense to me. I thought a user always had a schema?
Or does the user only get a schema if he owns an object? But, if that's the case, then why didn't he own any objects when I dropped him from the database where he had a schema?
Oh, one other important verification: I have noticed that if a user owns an object, it won't let you drop the schema, but I just want to verify: you can't accidently drop objects when you drop the schema, right?
Thx in advance...
August 30, 2011 at 2:03 pm
Let's see if I can help to clarify a few things:
I thought a user always had a schema?
Yes and no. A user always has a DEFAULT schema.
As per BOL:
WITH DEFAULT_SCHEMA = schema_name
Specifies the first schema that will be searched by the server when it resolves the names of objects for this database user.
But a user is not required to own a schema. On the other hand, there are users that actually own one ore more schema. This is specified when creating a schema and specify AUTHORIZATION. If AUTHORIZATION is not specified, the schema will be owned by dbo.
You can drop a user as long as this user does not own a schema in the related db. If the user owns a schema, the schema either need to be dropped or re-assigned to a different user.
You can't drop a schema as long as there are objects within this schema.
You might want to look at a schema as being a logical set of objects within a database. You could also create a separate db instead of using different schema. But you could easily drop such a db without any error whereas you can't drop a schema that contains objects.
if a user owns an object, it won't let you drop the schema
The fact that you cannot drop a schema if it contains an object has little to do with the user but with the object itself. Even when changing the owner of the object or the owner of the schema itself you wouldn't be able to drop the schema. All it has to do with is the fact there is an object in the schema. Ownership is (almost) irrelevant.
Dropping a schema just to be able to drop a user doesn't seem like the proper way to do it... I'd rather assign the schema to another user.
The easiest way to figure out how it works is to set up a test schema and a test user and play with it in a test environment.
Edit: It's interesting though that you can drop a schema that is assigned as a default schema to one or more users. You'll end up with an orphaned schema reference that should be cleaned up afterwards.
To quote BOL:
DEFAULT_SCHEMA can be set to a schema that does not currently exist in the database. DEFAULT_SCHEMA can be set before the schema that it points to is created.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply