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 4 years, 6 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 10 (of 10 total)

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

Cookies on SQLServerCentral

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