SQL to Drop All Non-System Objects From Master DB

We've all been there - well I have anyway. You've just created your script to generate all the required objects for your database.  You run the script in the new environment.  But wait, you forgot to set the database!  All the objects have been created in the master db instead!

The number of times this has happened to me and to others I have worked with is infuriating.  I've sat there watching people spend ages deleting one by one all the db objects using Management Studio, stumbling when there are dependencies, and generally wasting a lot if time.

To solve this problem I have put together a short script to generate all the required drop statements. Fortunately, the sys.objects table contains a handy "is_ms_shipped" column that makes life much simpler:

SELECT

'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+Name+']''))'+ CHAR(10)+ 'DROP '

+ CASE WHEN  type = 'U' THEN 'TABLE '

       WHEN  type = 'P' THEN 'PROCEDURE '

       WHEN  type ='FN' THEN 'FUNCTION '

       WHEN  type = 'V' THEN 'VIEW '

  END

+ Name + CHAR(10) + 'GO' + CHAR(10)

FROM  Master.sys.objects

WHERE is_ms_shipped <> 1

AND TYPE IN ('U','P','FN','V')

ORDER BY

CASE  WHEN type = 'P'  THEN 1

      WHEN type = 'V'  THEN 2

      WHEN type = 'FN' THEN 3

      WHEN type = 'U'  THEN 4

END

This script will generate all the DROP statements for the user-defined objects in the master db.  These need to be copied into a new window (easiest if you have been outputted the results to text rather than to grid in SSMS) and executed.

If there are any foreign key constraints, however, the above script will generate an error.  To handle this either execute all the drop statements over and over until the errors cease (each subsequent execution of the drop statements will delete additional tables as each foreign key table is dropped), or, prior to executing the above script, execute the following:

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name

+ CHAR(10) + 'GO' +CHAR(10)

FROM sys.foreign_keys fk

WHERE is_ms_shipped = 0

This will generate the necessary statements that need to be run in order to drop all the constraints. Copy the output to a new query window and execute.

The database principal owns a schema in the database, and cannot be dropped (Error: 15138)

On a recent audit of a database I noticed that there were a lot of logins for a database that were not required.  I set about removing the logins one by one, but soon hit the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Well this threw me a bit, and I could not figure out what was going on and why, this one particular login could not be removed.  As the message indicates, this account owns a schema on the DB. So I opened the user properties from SSMS and sure enough, the user  in question owned three schemas: db_datareader, db_datawriter, db_owner.  However, SSMS will not simply allow you to unselect the checkboxes here, so it is still not so easy to remove this link and allow the user to be deleted!

The schemas owned by the user can also be displayed by running the following query, within the context of the database under scrutiny:

SELECT s.name FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserInQuestion'');

For a more complete picture of all schemas and users associated with the database:

SELECT *, USER_NAME(principal_id) as username
FROM sys.schemas;


In order to remove this user we have to first transfer ownership of these schemas to a different user.  Typically this would be dbo.  This has to be done once for each schema owned, using the ALTER AUTHORISATION ON SCHEMA command:

 ALTER AUTHORISATION ON SCHEMA::db_datareader TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_datawriter TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_owner TO dbo

(note the double colon!)

Re-run the previous scripts to ensure that the user in question no longer owns any schemas.  Now if we again try the delete again, this user will be removed successfully.