I am relatively new to MSSQL environment (Only been working with it for little over a year). I’ve been charged with managing and developing a 2008 r2 Express that was upgraded from an Access Database last year. Management wants more accountability for what’s being changed and updated in their database. I need to be able to Audit updates, inserts, and etc for all of the tables. I’ve looked at adding an Audit trigger to each of the tables (side effects maybe performance). I’ve also looked at letting 3rd party software manage the Audits (Some are a bit pricey). Before I get to in-depth with my challenges, I need good advice on what direction to go with this. I’ve also thought of having the employer upgrade to the enterprise addition if the SSMS Auditing feature was worth it (Express doesn’t support this feature). Desperate is an understatement for my situation. (FYI – this is my very first post on any Q&A site, ever. I’m 43 years old and I generally can find answers on the internet or some version and make my own modification. This project has me stumped! Thanks!)
Welcome!! Hopefully we can provide some sense of direction!! You'll find there are a lot of wizards on this forum. I would probably start with asking what problem the audit's are trying to solve? If you have rampant changes, then you might need to investigate the security as it might need to be adjusted. Also, how are the changes being implemented? Do you have a change control process in place? How about code reviews? Some of these things are a culture change but can help save a lot of money in the long run. Does management just want to know when things change? Data changes too? For data changes, and without knowing how the changes happen, I'd suggest implementing stored procedures and again granting the appropriate folks the ability to execute the procedures. If the only thing that they can do is to execute procedures then they can only do whatever you code the procedure to do. I don't know if that's feasible, but wanted to at least throw it out there. I would also do a cost based analysis of moving to Enterprise. Depending on your environment, moving to that can be very pricey and might not be worth it to management. There can be a performance hit for table triggers and I usually try to stay away from them if possible. It also depends on how they are written and what they are doing as well as what your environment looks like. Hope this helps some!