How to Track Deletion of any Database

  • Hi

    I tried putting a trigger on Master..Sysdatabases

    But it doesnt allow me to do so.

    Is there any way to know when a particular database is deleted with the name of the Computer(Host_name())

    Can i restrict anyone from deleting Database.

    (All the users in my office use 'sa' login )

    Thanks

    awaiting reply

    Mayuresh

     

     


    MayurArt

  • This was removed by the editor as SPAM

  • I can't answer your query directly, but I would ask why the users need SA privs?

    I would determine the exact security level that the users need to perform their job (profiler could help with this) and reduce their security privs accordingly. If they are simply accessing the tables in a database, could you reduce them to db_datareader, db_datawriter and grant execute to any stored procedures. You would not have to worry about them deleting the database then. Or even dropping tables (although they could delete everything in a table...).

    Perhaps creating a role with all necessary privs in it, grant the role to the users and then remove their SA rights. Do this with a "friendly" user first to ensure it works.

    regards

    Neil.

  • As Neil said - grant users the permissions to do their jobs, not sysadmin memberhips.

    However, you can prevent casual database deletions with a trigger:

    CREATE TRIGGER STOP_DB_DROP

    ON ALL SERVER

    FOR DROP_DATABASE

    AS

    PRINT 'You are not allowed to DROP databases'

    ROLLBACK;

    If you take this route they can still delete the database by disabling this trigger. Remember that with permissions users start with nothing, and you grant permissions bit by bit until they're able to work and can't compromise the data or the system. Never grant everything and try to work backwards.

Viewing 4 posts - 1 through 3 (of 3 total)

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