September 5, 2025 at 1:27 pm
Hi, we have a few people who like to experiment on our prod sql server with new tables, schema changes, dependency chain breaks, maybe ssis, even ssrs etc etc without telling anyone till its too late. They hold high ranking positions so forget your first thought. I often end up with technical debt or sheer embarrassment simply being the last person to know. Is there a way to detect db, ddl, sql agent , proc, view, pkg, rdl etc etc changes automatically in sql server? This could also compliment our foray into devops which will include db object. but im not necessarily high on the idea of waiting that long.
i guess i can see how to detect new rdl's easily. Maybe deleted rdls as well. and maybe the modified via the catalog's mod date.
September 5, 2025 at 4:02 pm
I know you say to forget my first thought, but restrict access. Give them access to a test environment with close to live data so they can play around. My opinion - doesn't matter how high their ranking position is, if they are impacting production and you are taking the fall for it, restrict access.
As for detecting db, ddl, sql agent, proc, view, pkg, rdl, etc changes, there is always a way, but do you want to be bothered with it? SOMETHING needs to be scheduled to monitor various tables to watch for those changes. It may be a SQL monitoring solution, it may be an XE session, you could have a SQL job that does a count on sys.objects and emails you every 15 minutes to tell you how many objects exist in each DB. That last one is a bit of overkill; if you are going that route, I would recommend a stored procedure that writes to a table to say how many objects exist and the date/time it was run and returns the difference between the current and previous run and if it is big enough, sends out an email. Then set up a job to alert you on that. ALTERNATELY, almost ANY SQL Monitoring tool (Redgate Monitor for example; I don't work for them, just use their tool) can send out custom alerts, so you could have it count the number of objects in sys.objects and send an alert when the number increases.
You would need an XE session or something to monitor for changes vs create/drop operations mind you, and I expect anything you put in place would have an impact on system performance, so I'd be hesitant about that.
What it sounds to me like you need is a change management process. Work to implement that and get buy in from management and then those high ranking people will have no choice but to follow company policy. Our company has a policy that ALL code must undergo code review before it hits production. So nobody messes around randomly with production stuff or they risk getting written up or potentially terminated. Some departments even take it a step further and require proof of testing by someone other than the developer prior to go live. Policies and fear of termination usually get people to fall in line. Plus, for a lot of the different compliance certs (SOX, ISO, etc), change management may be required along with segregation of duties and often high ranking positions shouldn't be messing around in production databases...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply