We're building a SQL source control solution (using Redgate tools). The Ultimate idea is to use DEVOPS , GIT and build pipelines for all databases (about 400 very complex databases)
our issue is that we still have people developing on live servers (yes I know it's bad) , so we want to take a strategy of taking one database at a time and adding into GIT. I can't take sysadmin privileges away from the frontline bug fix teams... so people can still modify on live without checking into source control. 🙁
I want to prevent DDL events to individual databases unless it comes from the source control system... then roll out to more and more databases over time
I thought about using a database trigger with a rollback unless it comes from the deployment tools... but the event data in those triggers does not include ApplicationName
even worse, everything is windows authentication (including how you can "get latest"), so the only way I can see to block changes on databases migrated to source control is on the app name
anyone got any other ideas???