We are starting to integrate our SQL Server workflow with Git, by which I mean generating scripts for all objects and storing them in a Git repository so you can pull, edit, commit, push and apply, rather than just modify objects directly.
One problem we have is that certain objects (in this case, stored procedures) are more important, and we only want them to be altered by certain users (well, actually, certain AD groups which will have one or two users in them). However, to match our company's existing IT workflow, we were looking at using TeamCity and Octopus to automatically deploy changes to the database when the Git master branch is changes (which will occur via an approved pull request). We use Atlassian Bitbucket for hosting the repos.
Can this object-level restriction work with this Git workflow?
Things I've looked at:
- Bitbucket can allow default reviewers for certain branches, but you can't add a group, only individuals.
- Even if I use some sort of branch-based permission, I'm not sure how we could enforce branch naming conventions when a developer wants to alter one of these "protected" objects
- By using TeamCity and Octopus (in my understanding), no individual developer would have permission to change things in Live, but only the Octopus user has that permission. Thus, I'm not sure if you could have that one user only able to make changes to objects if a certain person approved the appropriate pull request.
- We've started playing with RedGate's SQL Source Control, but I think that whether we use this, or GitKraken, or any other source control interface tool, the problem won't change
- Maybe it can't be done with Octopus, and so we just set the object permissions normally, do our pull requests and all that, but rather than Octopus deploy the changes automatically, after you approve the pull request you then apply the object changes
To give an ideal example workflow I'm imagining:
- We have a schema Sales and a schema SalesGoldStandard. Any developer can alter objects in the Sales schema, but they want to alter a SalesGoldStandard stored procedure, so they create a branch called SalesGoldStandard-Issue135-Changes
- Change the .sql file and commit their change
- Push to the change to Bitbucket and create a pull request
- The pull request automatically selects John, in the "Sales Data Custodian" group, based on the branch name
- John is happy, approves the pull request, then TeamCity and Octopus do their thing and deploy the change
I can't find any questions here relating to this situation, or anything about Octopus, Bitbucket, or SQL Source Control that seems to help. I get the feeling it can't be done this way but maybe someone has solved this problem.