Cleaning up the SQL Server master database
By: Aaron Bertrand | Comments (4) | Related: More > System Databases
Problem
I'm sure we've all done this: inadvertently created objects in the master database. We may not have noticed we were in master, we may have left out a USE directive in our deployment script, or we may have been placed in that context without our knowledge (as in this Connect bug). At the end of the day, though, you're probably going to want to remove those user-created objects from the system database.
Solution
Without going into things like ensuring you haven't also inadvertently placed external dependencies on any of these objects (like an application that now talks to the master database instead of a user database, or even another database that depends on entities you placed in master), there is a specific strategy and order of operations you should follow when cleaning up system databases. I'm going to divide into two categories (things we won't be dropping and things we will be dropping) the types of things you might have inadvertently created in the master database:
Things we won't be dropping today
There are certain object types I'm not going to handle, either because you're simply not going to be able to drop them, or because I do not want to encourage dropping things that could be irreversible or leave your system in an undesirable state. Some of these, also, may be created in master on purpose:
ET | External table |
IT | Internal table |
PC | Assembly (CLR) stored procedure |
PG | Plan guide |
RF | Replication-filter-procedure |
S | System table |
SQ | Service queue |
TA | Assembly (CLR) DML trigger |
X | Extended procedure |
- | CLR User-Defined Data Type (UDDT) |
There are also some system objects that *look* like user objects that we're going
to want to avoid touching. While system objects shouldn't show up in sys.objects
,
a few do, and the list is version-dependent. This is the set I see in current builds
of SQL Server 2016:
SELECT [name], [type], [type_desc] FROM sys.objects WHERE [type] NOT IN ('IT','S') AND is_ms_shipped = 1 ORDER BY [name]; /* EventNotificationErrorsQueue SQ SERVICE_QUEUE MSreplication_options U USER_TABLE QueryNotificationErrorsQueue SQ SERVICE_QUEUE ServiceBrokerQueue SQ SERVICE_QUEUE sp_MScleanupmergepublisher P SQL_STORED_PROCEDURE sp_MSrepl_startup P SQL_STORED_PROCEDURE spt_fallback_db U USER_TABLE spt_fallback_dev U USER_TABLE spt_fallback_usg U USER_TABLE spt_monitor U USER_TABLE spt_values V VIEW */
You may also have created your own modules and marked them as system objects, in which case they might show up in the above list too. Note though that there really is no reliable way to distinguish such things from true system stored procedures, so you will have to have some other subjective way to identify those that shouldn't be there, or shouldn't be marked as system objects.
Some of the queries against catalog views may reference columns that only exist in SQL Server 2008 and above. I haven't tested the scripts against 2005, so it is possible you may need to make tweaks to use them against older, unsupported versions. I certainly use syntax that requires 2008+ (combined declare/set and +=).
And finally, there are some other scenarios where you may have adjusted master without meaning to, but that I'm going to shy away from here. Without making an extremely thorough list, I'm avoiding these scenarios either because they're one-off things (like the wrong compatibility level or recovery model), or because they are far too sensitive to allow for automated adjustment (such as cases where you added extra data files, or added a CLR assembly and associated aggregates and functions). I do not want to encourage any magic scripts or shortcuts to "fix" any of these miscues.
Things we will be dropping today
For the items we do want to consider dropping, there is definitely an important order of operations that will produce the least amount of headache. Foreign keys and views should be eliminated before any tables, for example, and partition functions can't be removed until any tables that use them are dropped. Also, in many cases, circular references can exist, so scripts may need to be executed multiple times until they succeed in full.
There are some object types that are simply dropped with the table they're in,
so there's no need to worry about them explicitly. You may see these items in
sys.objects
, but they will be gone once their tables are dropped. Note
that there are two kinds of "default" that will register as type = 'D'
in sys.objects
; a default constraint associated directly with exactly
one column, and the old (deprecated) CREATE DEFAULT
, defined at the
database level, that could be used against multiple columns. We'll deal with the
latter later.
In the meantime, these are the objects I don't need to worry about:
C | Check constraint |
D | Default constraint But not the old-style, stand-alone CREATE DEFAULT |
PK | Primary key constraint |
TR | DML trigger |
UQ | Unique constraint |
The rest are, in order, and acknowledging that this list might not be 100% exhaustive:
1. | F | Foreign key constraint | Need to be dropped before tables, as described below. |
---|---|---|---|
2. | V | View | Should be dropped before tables, since they can have direct
or indirect SCHEMABINDING . |
FN | SQL scalar function | ||
IF | SQL inline table-valued function | ||
TF | SQL table-valued-function | ||
3. | P | Stored procedure | Need to be dropped before tables, but after views and functions (since
procedures can reference views and functions, but can't be referenced by
them except in rare cases using OPENQUERY() ). |
4. | U | Table (user-defined) | Dropped after foreign keys and any type of module that can reference
them with SCHEMABINDING . |
5. | TT | Table type | Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types. |
Alias types | |||
6. | SO | Sequence object | Dropped after tables/modules because they can't be dropped if any table or module references them. |
R | Rule (old-style, stand-alone) | ||
D | Old-style CREATE DEFAULT |
||
Partition functions | |||
7. | Partition schemes | Can't be dropped until after partition functions. | |
8. | SN | Synonym | I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them). |
9. | Schemas | I drop these almost last because almost all entities above can belong
to a certain schema. For any entities you want to keep but move to a different
schema, you'll need to first use
ALTER SCHEMA ... TRANSFER . |
|
10. | Roles and Users | For users you don't want to keep, you'll need to first remove them from
user-defined role membership and ownership, as well as ownership of any
schemas. (For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles. |
The basic approach
Since there are many ways to have circular references, and since it can be complicated to determine the correct order to drop objects, I like to generate scripts that are repeatable - they try to drop objects, indicate if any fail, and can be run again and again until they succeed (which may require further intervention).
For example, if I have two views, one referencing the other with SCHEMABINDING
,
it should be possible to drop both of those views. If I guess the correct order
(or write a script that determines the correct order), I can do this by issuing
this pseudo-code:
try { drop view 1; go; drop view 2; }
If the views are listed in the wrong order, then running the same script
again should succeed (well, one view would fail because it doesn't exist, but
not because of the SCHEMABINDING
). If there is a chain of three or
four or more views then the script will need to be executed more times to succeed,
but unless there are other permission issues it will eventually have eliminated
all of the views. All of the scripts below look amazingly similar - build a statement
with dynamic SQL from some set of catalog views, wrap it in TRY/CATCH
,
and output the error message for any entity that fails.
The other thing I like to do is print out or select the SQL generated, so that I can review it and make sure I'm not making some change I don't really want to make. For shorter scripts it makes sense to ensure SSMS has been set to Results to Text, since Results to Grid eliminates carriage returns and line feeds, making the scripts harder to read and validate.
Even in Results to Text, the output can be truncated if you have a large number of objects to drop, and depending on your output settings in SSMS, it may not be possible to view the entire script at once. I've written about ways to get around this in another tip, "Validate the contents of large dynamic SQL strings in SQL Server." You may want to take the valid portion of the current output, execute that manually, and then when you run the original script again, it should output the next chunk of objects that haven't been dropped yet. Hey, I never said this would be simple; I'm just trying to make it a little bit easier. :-)
Let's get cleaning!
Please, before you start, please, please, please: Take a backup of master.
You may even want to restore it as a user database so that, in the event you delete
some entity that you really need to keep, you'll be able to recover it quickly.
This may not work for all entities that aren't stored at the database level; for
example, logins, which are stored in sys.server_principals
, will pull
from the current master, not the copy. But it should work for most other things,
like users, partition-related entities, and user objects like tables, stored procedures,
sequences, and user-defined functions.
Foreign keys
There are the first set of objects that I drop, simply because it is easy to do, and it has to be done before any of the underlying tables can be dropped. I have written more elaborate scripts around this before, but in this case it is simpler because I am not concerned about re-creating them. Again, we could go to great lengths to determine how to drop them all in the right order, but there still might be a circular reference to deal with, so why bother? Just run the output over and over again until no errors are returned.
-- script to drop Foreign Keys USE [master]; GO SET NOCOUNT ON; DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY ALTER TABLE ' + objectname + N' DROP CONSTRAINT ' + fkname + N'; END TRY BEGIN CATCH SELECT N''FK ' + fkname + N' failed. Run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT fkname = QUOTENAME(fk.[name]), objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) FROM sys.foreign_keys AS fk INNER JOIN sys.objects AS t ON fk.parent_object_id = t.[object_id] INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] ) AS src; SELECT @sql; --EXEC sys.sp_executesql @sql;
Note: Some of these scripts will not work 100% correctly if you have entity names containing single quotes or square brackets. Don't do that.
Views and functions
Views can reference functions, functions can reference views, views can reference other views, and functions can reference other functions; to add to the fun, all of these can include circular references. As with foreign keys, rather than try to produce a script that will reliably drop all such objects in proper dependency order (which might be impossible), I generate scripts that can be executed multiple times until they generate no output and reveal no errors. If you get the same output after repeated execution, there is probably a clue in the error message about why a certain object can't be removed.
-- script to drop views USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY DROP VIEW ' + objectname + N'; END TRY BEGIN CATCH SELECT N''View ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name]) FROM sys.views AS v INNER JOIN sys.schemas AS s ON v.[schema_id] = s.[schema_id] WHERE v.is_ms_shipped = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
-- script to drop functions USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Function ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_ms_shipped = 0 AND o.[type] IN ('FN','IF','TF') ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Stored Procedures
The script to generate DROP
statements for procedures looks awfully
similar to the one for dropping functions. Since stored procedures can reference
each other, it is still useful to use TRY/CATCH
and re-run the script
if any individual statements fail. Please double- and even triple-check this list
to make sure that you are only dropping procedures you really want to drop, or that
you have copies of them tucked away somewhere.
-- script to drop procedures USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Procedure ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'P' AND o.is_ms_shipped = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Tables
Now we can remove any user tables inadvertently created in master. As with procedures, please triple-check this list and make sure you know these should all be removed or that you have backups somewhere just in case.
-- script to drop user tables USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Table ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.tables AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_ms_shipped = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Table Types
Table types are found in sys.types
with is_table_type = 1
,
therefore generating DROP
scripts for these is relatively straightforward
(and must be done before alias types, since a table type could have a column defined
with an alias type):
-- script to drop table types USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Type ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.types AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_table_type = 1 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Alias types
Once tables, modules, and table types have been removed, we should be able to remove alias types, since there should be nothing left with a hard dependency on them. Note that alias types need to be removed before any rules bound to them can be dropped.
-- script to drop alias types USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Type ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.types AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.user_type_id > 256 AND o.is_table_type = 0 AND o.is_assembly_type = 0 -- not a CLR UDT ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Sequences
Next, we can move onto sequences, since there should no longer be any tables or modules that reference them:
-- script to drop sequences USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Sequence ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.sequences AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_ms_shipped = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Old-style CREATE RULE
Back in early versions of SQL Server, there was a standalone type of check constraint
called a rule. You could associate a Rule with an alias type or directly to a column
using sp_bindrule
. Once all alias types and columns have been dropped,
it should be safe to drop all rules.
-- script to drop old-style Rules USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Rule ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_ms_shipped = 0 AND [type] = 'R' AND parent_object_id = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Old-style CREATE DEFAULT
Quite similar to old-style rules, it should now be safe to drop old-style defaults,
even if they had been bound to alias types or columns using sp_bindefault
.
-- script to drop old-style Defaults USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Default ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE o.is_ms_shipped = 0 AND [type] = 'D' AND parent_object_id = 0 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Partition functions
With no user tables left, it should be safe to eliminate partition schemes and functions. We should drop functions first since that is the only way we'll be able to drop the partition schemes (unless a scheme was created and left unused).
-- script to drop partition functions USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Partition function ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME([name]) FROM sys.partition_functions ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Partition schemes
And now with partition functions out of the way, we can safely drop any partition schemes. As I alluded to before, I'm not going to help build out scripts that will drop partition-related filegroups, because I think that's a little too dangerous.
-- script to drop partition schemes USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME([name]) FROM sys.partition_schemes ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Synonyms
As mentioned above, synonyms should be relatively painless to remove, but as with any other entity, there's no way for a script running against the current SQL Server instance to possibly know of any external code referencing these synonyms. So please be sure these are actually safe to drop.
-- script to drop synonyms USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Synonym ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name]) FROM sys.synonyms AS o INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id] ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
Schemas
At this point, it should be safe to drop any user-defined schemas you have inadvertently created, as long as you have dropped (or transferred) all objects they owned. These may fail, of course, if a schema owns any of the entity types we haven't touched, like assembly_types or service_queues.
-- script to drop schemas USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';''; END TRY BEGIN CATCH SELECT N''Schema ' + objectname + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM ( SELECT QUOTENAME(s.[name]) FROM sys.schemas AS s WHERE [schema_id] BETWEEN 5 AND 16383 ) AS src(objectname); SELECT @sql; --EXEC sys.sp_executesql @sql;
For any schemas that no longer exist, you should update the users in the event
any of them have specified those schemas as default. The dropped schema will still
be reflected in sys.database_principals
, and will lead to unexpected
errors if the user tries to create an object without explicitly naming the schema.
-- script to re-route default schemas USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo; END TRY BEGIN CATCH SELECT N''User ' + p.name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.database_principals AS p WHERE p.default_schema_name IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM sys.schemas AS s WHERE name = p.default_schema_name ); SELECT @sql; --EXEC sys.sp_executesql @sql;
Roles and Users
This is where chicken and egg can be the worst, because users can be role members
and role owners, and roles can be role owners and members too, and users and roles
can own schemas you've kept. So you need to first transfer any user-owned roles
to dbo
(or any other user/role you'll keep), then transfer authorization
of the schemas, then drop all of the members of all the roles, then drop the roles,
and then finally drop the users.
-- script to change ownership of roles to dbo USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo; END TRY BEGIN CATCH SELECT N''Role ' + r.name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.database_principals AS r INNER JOIN sys.database_principals AS u ON r.owning_principal_id = u.principal_id WHERE r.[type] = 'R' AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys') AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way! AND r.is_fixed_role = 0; SELECT @sql; --EXEC sys.sp_executesql @sql;
-- script to change ownership of schemas to dbo USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo; END TRY BEGIN CATCH SELECT N''Schema ' + name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.schemas WHERE [schema_id] BETWEEN 5 AND 16383 AND principal_id BETWEEN 5 AND 16383; SELECT @sql; --EXEC sys.sp_executesql @sql;
There's an added wrinkle that in SQL Server 2012 you should use ALTER ROLE
to remove members, but in earlier versions you need to use sys.sp_droprolemember
.
-- script to remove members from roles USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY ' + CASE WHEN CONVERT(tinyint, PARSENAME(CONVERT(nvarchar(128), SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+ THEN N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER ' + QUOTENAME(m.name) + N';' ELSE N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name + ''', @membername = N''' + m.name + N''';' END + N' END TRY BEGIN CATCH SELECT N''Role ' + r.name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.database_role_members AS rm INNER JOIN sys.database_principals AS r ON rm.role_principal_id = r.principal_id AND r.is_fixed_role = 0 INNER JOIN sys.database_principals AS m ON rm.member_principal_id = m.principal_id; SELECT @sql; --EXEC sys.sp_executesql @sql;
-- script to drop roles USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY DROP ROLE ' + QUOTENAME(r.name) + N'; END TRY BEGIN CATCH SELECT N''Role ' + r.name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.database_principals AS r WHERE r.[type] = 'R' AND r.name <> N'public' AND r.is_fixed_role = 0; SELECT @sql; --EXEC sys.sp_executesql @sql;
And finally, we can drop the users:
-- script to drop users USE [master]; GO DECLARE @sql nvarchar(max) = N''; SELECT @sql += N'BEGIN TRY DROP USER ' + QUOTENAME(u.name) + N'; END TRY BEGIN CATCH SELECT N''User ' + u.name + N' failed - run the script again.'', ERROR_MESSAGE(); END CATCH ' FROM sys.database_principals AS u WHERE u.[type] IN ('U','S') AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys') AND u.name NOT LIKE N'##%##'; SELECT @sql; --EXEC sys.sp_executesql @sql;
...and that should pretty much do it.
Summary
Cleaning master can be a very messy and manual affair. While you can automate writing out metadata commands to drop objects and other entities in a "safe" order, nothing can substitute for qualitative analysis - a human should be absolutely certain an entity doesn't belong in master before deleting it. So please don't use this code as a model to blindly let rip on all your servers.
I've seen a lot of systems with polluted master databases, and I've helped clean up quite a few, but can't possibly claim to have seen everything. Did I miss any entity types? Do you have a scenario where my prescribed order doesn't work? Do you think I'm doing anything dangerous here? Let me know in the comments below!
Next Steps
- See these tips and other resources:
- While I aimed for simplicity, to make some of these metadata queries even simpler, you could probably look at some of the views I demonstrated in an earlier tip, Making SQL Server metadata queries easier with these new Views.
- If you have issues with a lot of user entities in master, it might be useful to review Validate the contents of large dynamic SQL strings in SQL Server.
- An earlier tip showed a technique you can use to script out
CREATE
statements before deleting objects, so that they can be re-generated if dropped inadvertently: Drop and Re-Create All Foreign Key Constraints in SQL Server. This is more challenging with complex entities like tables than for simpler objects like constraints, but the principles are the same. - The official documentation on catalog views can be quite helpful when determining the predicates you should use when generating scripts that should include or exclude certain types of objects. Those mentioned above:
About the author

View all my tips