DROP SCHEMA script for multiple sql database users

Question:There’s a requirement to drop multiple SQL Server database users  as part of a clean up. Those database users also own schemas.   I’d like to generate a script which creates the SQL code, rather than manually deleting the users. How can I generate the script?

Answer:The steps you’ve requested can be achieved by sql code. In a previous post Msg 15138 - The database principal owns a schema in the database and cannot be dropped. , there are details of a common scenario where a DBA attempts to execute a DROP USER command – only to discover the database users owns a schema.

This script will generate a  list of DROP SCHEMA DROP USER  statements , based on the database users whose name starts with ‘n’.

Script DROP SCHEMA for multiple sql database users

use MY_DB
go
select 'DROP SCHEMA ['+name+'] DROP USER ['+name+'] ' from sys.sysusers where name like 'n%'
--results
--DROP SCHEMA [name1] DROP USER [name1] 
--DROP SCHEMA [name2] DROP USER [name2] 
--DROP SCHEMA [name3] DROP USER [name3]

When you attempt to run the DROP SCHEMA DROP USER code , you may find an error message such as You cannot drop schema name1 because it is referenced by object xxxxx.

You’ll need to analyse the objects. Typically it’s objects within the schema such as tables.   The objects need to be disassociated from the schema . You could either drop them or move them to another schema.

It is important to plan any  security or object changes. I usually script out the objects prior to deleting or take a database backup prior to the change.

Read More on Object management

Msg 5075 - The database collation cannot be changed if a schema-bound object depends on it

SQL Server - ALTER SCHEMA - SQL Server DBA

How to select tables in a schema using sysobjects - SQL Server DBA