November 10, 2020 at 1:15 pm
Great stuff man! Thank you for sharing.
November 10, 2020 at 2:26 pm
What is the syntax to drop the trigger,
DROP TRIGGER trgBlockMasterUserObjects
Since the above does not work
Doug
November 10, 2020 at 2:40 pm
To drop the trigger you need to use the "ON DATABASE" or "ON ALL SERVER" depending if its a database or server scoped DDL trigger
USE [master]
GO
DROP TRIGGER trgBlockMasterUserObjects ON DATABASE
GO
November 10, 2020 at 9:09 pm
Comments posted to this topic are about the item Block user objects from being created in a master database
November 10, 2020 at 9:12 pm
Sorry. corrected now. thanks for the feedback.
November 12, 2020 at 11:11 pm
How do I determine if there are already objects accidentally in master?
November 17, 2020 at 4:41 am
here is the code for that:
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')
November 17, 2020 at 4:58 pm
Thank you!
November 17, 2020 at 6:44 pm
This is a little quicker
SELECT OBJECT_SCHEMA_NAME( OBJECT_ID) AS [Schema], Name
FROM sys.All_OBJECTS AS AO
WHERE OBJECT_SCHEMA_NAME( OBJECT_ID) NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
AND OBJECT_SCHEMA_NAME( OBJECT_ID) = 'dbo'
AND LEFT(name,3) NOT IN ('sys', 'spt')
AND LEFT(name,5) NOT IN ('sp_MS', 'sp_ss', 'MSrep')
AND type_desc NOT IN ('INTERNAL_TABLE','SERVICE_QUEUE')
ORDER BY Name;
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy