SQL Change Automation (TFS plugin): Dropping Users during deployment

kevin_neumannkevin_neumann Posts: 2 New member
edited November 8, 2018 2:45PM in SQL Change Automation
We're using the TFS Plugin for SQL Change Automation 3, and we've got a build and release pipeline set up. During the SQL CA Release step, we're seeing roles and users getting dropped from the destination database, even though we've included the IgnoreUsersPermissionsAndRoleMemberships flag in the SQL Compare options. 

Our verbatim options are as follows:
IgnoreFillFactor, IgnorePermissions, IgnoreWhiteSpace, IgnoreBindings, IgnoreIdentitySeedAndIncrement, IgnoreCertificatesAndCryptoKeys, IgnoreUsersPermissionsAndRoleMemberships, IgnoreUserProperties, IgnoreWithElementOrder, IgnoreReplicationTriggers, IgnoreNotForReplication, IgnoreDatabaseAndServerName, IgnoreSchemaObjectAuthorization, DecryptPost2KEncryptedObjects, IgnoreTSQLT

Are we specifying these options incorrectly, or is there some option we're missing? We've tried with and without spaces after the comma, but that didn't seem to make a difference. 

Best Answer

  • chris_godfreechris_godfree Posts: 50 Silver 1
    Are you using a filter file and is the same file being used when you commit objects to source control and during your build & release pipelines? From when I first set-up our TFS pipelines and a user was being dropped, I think the only way to exclude users/roles is via a filter rule. The option you mention will not exclude users, only their permissions.

Answers

  • teetee Posts: 25 New member
    We are also looking for the best practice solution for ignoring the users and roles. Ensuring that the filter includes the required exclusions seems like a good option; however, in VS 2017 I am not able to load a filter.The documentation instructs to place the scpt file in the root (project) folder and then 'unload' and 'load' the project. Loading and unloading the project in VS 2015 works, but I am unable to get the scpf file to load into a VS 2017 project. 
  • teetee Posts: 25 New member
    edited October 4, 2019 3:12PM
    unintended post
  • chris_godfree, that makes sense. We didn't even have a filter specified, but once we specified a filter with users excluded it worked like a charm!
Sign In or Register to comment.