Background

Early this morning I initiated a process to move a database from Production to QA.

As I tried to drop the users from the originating environment, I ran into the error pasted below:

drop user [LAB\ITV-IIS01$]
Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

Origin

I know that the schema mentioned is a shell.  It was simply created as part of our user creation script.

Using SQL Server Management Studio, we can script the user creation script.

And, here is a sample.

CREATE USER [LAB\ITV-IIS01$]
FOR LOGIN [LAB\ITV-IIS01$] 
WITH DEFAULT_SCHEMA=[LAB\ITV-IIS01$]
GO

Remediation

Let us programmatically identify and script out those users.

SQL

Stored Procedure

dbo.sp_ScriptDropUsersWithSchemas

use master
go

if object_id('[dbo].[sp_ScriptDropUsersWithSchemas]') is null
begin

	exec('create procedure [dbo].[sp_ScriptDropUsersWithSchemas] as ')

end
go

-- exec sp_helpuser
alter procedure [dbo].[sp_ScriptDropUsersWithSchemas] 
as
begin

	;  with cteSystemUser
	(
		[username]
	)
	as
	(
		select 'dbo'
		union
		select 'guest'
		union
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteSystemSchema
	(
		[schema]
	)
	as
	(
		select 'INFORMATION_SCHEMA'
		union
		select 'sys'
	)
	, cteObjectSchema
	(
		  [schema]
		, [object]
		, [schemaOwnerID]
		, [schemaOwnername]
	)
	as
	(
		select 
				  [schema] = tblSS.[name]
				, [object] = tblSO.[name]
				, [schemaOwnerID] = tblSS.principal_id
				, [schemaOwnername] = user_name(tblSS.principal_id)

		from   sys.schemas tblSS

		inner join sys.objects tblSO

				on tblSS.[schema_id] = tblSO.[schema_id]

		/*
			Skip System Schemas
		*/
		where  tblSS.[name] not in
					(
						select [schema]
						from   cteSystemSchema
					)
	)
	, cteUser
	(
		  [username]
		, [usertype]
		, [userSchemaDefault]
		, [userCreateDate]
		, [schema]
		, [sqlCommandDropSchema]
		, [sqlCommandDropUser]
	)
	as
	(
		select 
				  [username] = tblSDP.[name]
				, [usertype] = tblSDP.[type_desc]
				, [userSchemaDefault] = tblSDP.default_schema_name
				, [userCreateDate]= tblSDP.[create_date]

				, [schema] = tblSS.[name]

				, [sqlCommandDropSchema]
					= 'if schema_id(''' + tblSS.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop schema '
							+ quoteName(tblSS.[name])
							+ ''' '
							+ '; '
							+ ' DROP SCHEMA '
							+ quoteName(tblSS.[name])
							+ ' end '

				, [sqlCommandDropUser]
					= 'if user_id(''' + tblSDP.[name] + ''')' 
							+ ' is not null '
							+ ' begin '
							+ ' print ''drop user '
							+ quoteName(tblSDP.[name])
							+ ''' '
							+ '; '
							+ ' DROP USER '
							+ quoteName(tblSDP.[name])
							+ ' end '

		from   sys.database_principals tblSDP

		left outer join sys.schemas tblSS

				on tblSDP.[name] = tblSS.[name]

		where tblSDP.[type_desc] in
				(
					  'SQL_USER'
					, 'WINDOWS_USER'
				)

		and  tblSDP.default_schema_name not in
				(
					'dbo'
				)
	)

	select 
			cteU.*

	from   cteUser cteU

	left outer join cteSystemUser cteSU

		on cteU.username = cteSU.username

	/*
		Skip System Users
	*/
	where cteSU.[username] is null

	/*
		Ensure that user does not own objects
	*/
	and not exists
		(	
	
			select 1

			from   cteObjectSchema cteOS

			where  cteU.[username] = cteOS.[schemaOwnername]

		)

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUsersWithSchemas]'
go

Output