Permissions dropped via CLI that aren't flagged as differences

DylanMusil

I am comparing between a sqlproj and a remote database using the following command line switches.

                & "C:\Program Files (x86)\Red Gate\SQL Compare 14\sqlcompare.exe" `

                "/Scripts1:'path\to\sqlproj'" `

                "/Database2:'dbname'" `

                "/Server2:'servername'" `

                "/UserName2:'username'" `

                "/Password2:'password'" `

                "/exclude:Additional" `

                "/include:StoredProcedure" "/include:Function" "/include:View" "/include:Identical" `

                "/report:Report.html" "/ReportType:html" `

                "/synchronize" `

                "/ScriptFile:Deploy.sql" `

                "/verbose"

My goal is to only deploy changes to stored procedures, views, and functions that already exist in both the source and destination or are new in the source. The synchronize operation should not drop anything. My understanding is that /exclude:Additional handles that for me.

However since activating this last week, I've seen almost 50 permissions being dropped randomly. Sometimes when the command runs nothing is dropped, sometimes 5-10 permissions get revoked. The permissions being revoked are scripted as follows:

REVOKE VIEW CHANGE TRACKING ON  [schema].[table] TO [user]

When I inspect the report these permissions are not shown as differences. I am using SQL Compare V14.5.22.19589

Is this intended behavior based on the switches I have selected? Do I need to use an exhaustive set of excludes? Why is the behavior of the drops erratic?

At one point even some sequences were altered using the above switches. How does that happen?

DylanMusil

I think one issue is that the options weren't being set correctly. According to the documentation the tool should detect that I'm running against an SSDT project and ignore permissions. That wasn't the case. Since I've set those options myself, I'm not seeing permissions being dropped anymore.

However sequences are still getting altered even after adding "/exclude:Sequence"

DylanMusil

Looks to be related to the Default option set including dependencies. I've removed that so we'll see how things go now.

DylanMusil

To summarize what I think happened:

• Permissions were dropped because there were some that existed in the target but not the source. It's confusing that /exclude:Additional didn't prevent this.
• Sequences were altered because objects were deployed that had sequences that were dependencies. Again it's confusing (and I think undocumented) that IncludeDependencies overrides /excludes.
  

Dan_J

Hi @DylanMusil

Thanks for your updates on this, it appears you have come to a conclusion on this. I will pass back your comment around the undocumented behavior of IncludeDependencies overriding /excludes to our development team.