DROP SCHEMA script for multiple sql database users

12 February,2015 by Jack Vamvas

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

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DROP SCHEMA script for multiple sql database users


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer