Finding user tables in SQL Server system databases

Category: Reliability
Item: User tables in sys DBs

Why should you care about them?

User datatables  should not be  in the system databases (master, msdb).

In case of failure, there is a high chance that those tables would be lost.

In the case of migration, these tables may be forgotten and not restored.

It shows that some users may have excessive access rights to system databases.

Also, maybe someone is running a query or pointing third party tool at the wrong database, deploying some objects, and not cleaning up after.

How can you find user-created tables in the SQL Server system databases?

You can use the script below to list all user tables in master and msdb databases.

  2. 'msdb' AS SysDatabaseName ,
  3. name AS UserTable
  4. FROM    msdb.sys.tables
  5. WHERE   is_ms_shipped = 0
  6. AND name NOT LIKE '%DTA_%'
  7. UNION
  8. SELECT 
  9. 'model' AS SysDatabaseName ,
  10. name AS UserTable
  11. FROM    model.sys.tables
  12. WHERE   is_ms_shipped = 0;

How to fix them?

  1. Rename tables. If no problems after X (30?) days, delete them.
  2. It could also fix security, so this can’t happen again.
  3. Ideally, you want to add automated DDL (any object modifications) monitoring to log all.

More information

Microsoft – msdb database.
Microsoft- model database.

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *