Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with.
Few days ago someone(some hacker or some previous developer) had truncated my important table causing inconvenience to my customers. I need to change my database password, server's password and other application's password in order to avoid such obnoxious actions and prevent my application.
Additionally first I need to know if this kind of activity is being performed with my data before my customers so that I can find out any work around for them. This is what I thought till now-
- I should create a window service which will run in every 1 hour and check in all tables or most importantly in main tables that # of records are as expected or not. If not then an alert will be sent to me, so that I can quickly fix that.
- I should create an SQL job which will perform same steps that I mentioned in first point.
These workarounds seems fine and workable. but I'm really curious to know what else can I do to prevent my data or alert me in case of data lost. I'm sure there are other efficient and more feasible way to achieve what I want. It would be great if anyone can help me around.
This is a web application and only authenticated users can login. My SQL Server edition is Enterprise.