I’m going to assume that you already know how to block user objects from being created in a master database (see my previous blog here – https://datasteve.com/2020/10/23/block-user-objects-from-being-created-in-a-master-database/.
I have a trigger now that prevents new user objects from being created, but what how do I discover any existing objects, i.e. objects that are probably created by mistake before the trigger (Fig #1)?
The solution is fairly simple. You run a query against all the objects in a master database while excluding all the known system objects. The following piece of code is going to uncover all the user objects that are currently in a master database.
select smas.name, objs.name, * --, left(smas.name,3) from master.sys.objects as objs inner join master.sys.schemas as smas on objs.schema_id = objs.schema_id where 1=1 --and smas.name NOT IN ('sys', 'INFORMATION_SCHEMA') and smas.name = 'dbo' and left(objs.name,3) NOT IN ('sys', 'spt') and left(objs.name,5) NOT IN ('sp_MS', 'sp_ss', 'MSrep') and objs.type_desc NOT IN ('INTERNAL_TABLE','SERVICE_QUEUE')
Running that code on my machine will bring back a user stored procedure that I created by mistake long time ago (Fig #2) for my first ever blog post – https://datasteve.com/2019/09/11/verify-your-database-backups/.
This blog is partially based on the following resources: