How often are you deleting users and you get the warning that this user owns a schema and the schema is going to get dropped also…and are you sure you want to do this? … And no, you’re not really sure about the schema? Well, schemas get ‘magically’ created along with users (that’s another story). But, what’s a reasonable way to deal with this dilemma? Actually, there are several quick, simple steps:
1. Does the schema have any objects in it? If not, then OK, just let it get dropped along with the user. Here are two queries to answer that question.
select schema_name(schema_id) as ObjectName ,count(*) from sys.objects groupby schema_name(schema_id)
select schema_name(schema_id) as ObjectName, name,type,type_desc from sys.objects where schema_name(schema_id)=‘procure’
2. If there are objects belonging to the schema, and you want to keep those objects… then transfer the objects to another schema (like “DBO”):
ALTER AUTHORIZATION ON SCHEMA::SCHEMAName TO NewOwnerofSchemaUserName
3. Finally, you’ll be able to: “DROP USER [webreader]”