Post reply

How to restrict DBowner to Drop Database?

  • May 14, 2010 at 2:37 am

    #232594

    Can DBowner ABle Drop Database?

  • May 14, 2010 at 3:06 am

    #1167378

    A db_owner can do anything to the database, including drop it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 17, 2010 at 7:35 am

    #1181555

    However you could create a DDL trigger at instance level to stop people from dropping dbs ad-hoc (a wise practice perhaps for live servers)

    Cheers,

    JohnA

    MCM: SQL2008

  • June 18, 2010 at 8:51 am

    #1182187

    John

    could you let me know how to do the trigger ?

  • June 18, 2010 at 10:35 am

    #1182253

    http://msdn.microsoft.com/en-us/library/ms186406.aspx

  • June 21, 2010 at 4:57 am

    #1182653

    Furthermore, if you are still stuck this is the code I use on some of my critical instances.

    This code also sets the event as 'logable' so you'll also get an alert when it happens (if you have set up appropriate alerts of course!)

    Cheers,

    John

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TRIGGER [ddl_trig_Prevent_Drop_DB]

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    --log attempt to drop database

    DECLARE @db VARCHAR(209)

    SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +

    ' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))

    RAISERROR(@db, 16, 1)WITH LOG

    --prevent drop database

    ROLLBACK

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [ddl_trig_Prevent_Drop_DB] ON ALL SERVER

    Cheers,

    JohnA

    MCM: SQL2008

Viewing 6 posts - 1 through 5 (of 5 total)

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