0

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:

  1. Bitbucket can allow default reviewers for certain branches, but you can't add a group, only individuals.
  2. 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
  3. 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.
  4. 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
  5. 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:

  1. 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
  2. Change the .sql file and commit their change
  3. Push to the change to Bitbucket and create a pull request
  4. The pull request automatically selects John, in the "Sales Data Custodian" group, based on the branch name
  5. 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.

  • So you want to have different pull request approvers based on the filename? – Ewan Apr 18 at 8:21
  • and in fact, its not the file name but the content of the sql which you really need to check! – Ewan Apr 18 at 8:22
  • could you have different databases rather than schema? – Ewan Apr 18 at 8:23
  • Technically it's the contents of the file we want to check, but since it's a certain stored procedure to restrict, the name of which will be the name of the file, the necessary approvers would be based on the file names. – Dylan P. Apr 23 at 0:33
1

We took our DB objects and created a separate repo for them. In this repo we only allowed the DBA group permissions to merge/change the deployment branch. We did allow others to do pull requests for review by the DBA group.

We also had teamcity set to use different deployment accounts per environment and only certain people had permission to start deployments at each level. Devs could start a deployment on Dev, QA could start a deployment on QA, DBA could start a DB deployment on Staging and Prod but could not do an application deployment. CM/IT could do app and DB deployments on Staging and Prod. I dont know all the implementation details from our setup (CM guys did it but we DBA had to make sure it worked for our stuff) but I know we had different jobs for each environment. This also helped the CM guys as it gave them a multiple environments to test changes to the deployment scripts before we broke prod deployments.

I dont know much about redgate sql source control but it sounds redundant if you already have a working code repository.

  • Using different repos is an interesting idea. I hadn't thought of that. I'll need to think a bit about your response, Effreem. Thank you. – Dylan P. Apr 23 at 0:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.