SQL server:warning when editing or changing schema.

Question

SQL server:warning when editing or changing schema.

IT researcher

SSCertifiable

Points: 7600
More actions
April 13, 2013 at 6:22 am

#292100

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?

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

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

Steve SSCommitted Points: 1844 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

Steve SSCommitted Points: 1844 More actions · Answer 3

I asked a similar question some time ago.

Maybe this thread will help.

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

Steve

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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)