Does sp_dropuser “do the right thing” with schemas?

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'
go

use adventureworks
go

sp_grantdbaccess 'bob'
go

select * from sys.schemas  — bob schema is there
go

sp_revokedbaccess 'bob'
go

select * from sys.schemas  — bob schema is gone
go

The more intriguing thing is when you check this by using the OBJECT_DEFINITION system function:

— creates schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_grantdbaccess'))

— drops schema
PRINT OBJECT_DEFINITION(OBJECT_ID('sys.sp_revokedbaccess'))

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)

Hmmm….

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.