Here's another blog posting to answer a question from over a month ago.
With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA named after the user and assign that SCHEMA as the user's default schema. Question was, "does sp_dropuser do the right thing and drop the schema named after the user?"
Short answer is "yes, it does". Pretty easy to prove. Actually, sp_adduser can eventually call sp_grantdbaccess and sp_dropuser eventually can call sp_revokedbaccess, so…
create login bob with password = 'A^#DNEfdfhkWD#*iubdwc )000ks1'
select * from sys.schemas — bob schema is there
select * from sys.schemas — bob schema is gone
The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:
– creates schema
– drops schema
Sure enough, there is nicely commented code in sp_revokedbaccess to drop a schema. BUT rather than using the DROP SCHEMA DDL statement, it looks like this:
EXEC %%Owner(Name = @name_in_db).DropSchema(OwnerType = 1)