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

kevin_neumann

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. 

chris_godfree

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.

tee

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. 

tee

unintended post

kevin_neumann

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!