SQL server:warning when editing or changing schema.

  • In our production sql server we have so many important databases. I have created two login 'admin'(has all permission) and 'read'(has only public and datareader permission) . I have done this only because when i want to see or read the database i use login 'read' so that there is no chance of any mistakes happening like deletion,rename etc when i access in SSMS.(Sometimes it happens i by mistake press f2 button and then some button and table name gets renamed).

    So i use the 'read' login.When i required to change table columns etc then i use 'admin' login. So i use 'admin; login only when i required and otherwise i use 'read' login only for protecting data.

    So do i have any option by which i can use my 'admin' login always , but whenever any schema(like column rename,delete) changes i want a alert so that i can came to know about it. Is there any option for doing it?

  • From an alerting perspective you may have to start a job that checks the default trace for these events and sends an email when they occur.

    It may be a little "noisy" at first until you have your filter built properly.

    SQL 2008 has a reporting feature so that you can see all schema changes for a DB as long as it is still in the trace.

    Steve

  • Do you mean like to have a warning type message box pop up to be sure that the action you are about to do is what you really want to do? Not that I know of.

  • I asked a similar question some time ago.

    Maybe this thread will help.

    http://www.sqlservercentral.com/Forums/Topic935472-392-1.aspx

    Steve

  • winmansoft (4/13/2013)


    In our production sql server we have so many important databases. I have created two login 'admin'(has all permission) and 'read'(has only public and datareader permission) . I have done this only because when i want to see or read the database i use login 'read' so that there is no chance of any mistakes happening like deletion,rename etc when i access in SSMS.(Sometimes it happens i by mistake press f2 button and then some button and table name gets renamed).

    So i use the 'read' login.When i required to change table columns etc then i use 'admin' login. So i use 'admin; login only when i required and otherwise i use 'read' login only for protecting data.

    So do i have any option by which i can use my 'admin' login always , but whenever any schema(like column rename,delete) changes i want a alert so that i can came to know about it. Is there any option for doing it?

    Wouldn't such an alert be after the fact? After the damage has already been done?

    I've not tried it myself but wouldn't it be better to add a DDL trigger on the table to prevent the change unless you specifically disable the DDL trigger?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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