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