Block user objects from being created in a master database

  • Great stuff man! Thank you for sharing.

  • What is the syntax to drop the trigger,

    DROP TRIGGER trgBlockMasterUserObjects

    Since the above does not work

    Doug

  • 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
  • That worked DATABASE needs to be DATABASE not master.

    Doug

    • This reply was modified 2 months, 1 week ago by  SQLOzzie.
  • Comments posted to this topic are about the item Block user objects from being created in a master database

  • Sorry. corrected now. thanks for the feedback.

  • How do I determine if there are already objects accidentally in master?

  • 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')

  • Thank you!

  • 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