Post reply

Block user objects from being created in a master database

  • November 10, 2020 at 1:15 pm

    #3806327

    Great stuff man! Thank you for sharing.

  • November 10, 2020 at 2:26 pm

    #3806385

    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

    #3806389

    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 3:17 pm

    #3806412

    That worked DATABASE needs to be DATABASE not master.

    Doug

    • This reply was modified 3 years, 5 months ago by  SQLOzzie.

  • November 10, 2020 at 9:09 pm

    #3804681

    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

    #3806563

    Sorry. corrected now. thanks for the feedback.

  • November 12, 2020 at 11:11 pm

    #3807340

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

  • November 17, 2020 at 4:41 am

    #3808538

    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

    #3808781

    Thank you!

  • November 17, 2020 at 6:44 pm

    #3808892

    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 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply