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