Roles not being tracked properly

ejenkins

Noticed recently that Roles are not showing up in either the Commit or Get Latest windows. Coincidentally we're also getting messages constantly about uncommitted dependencies when applying changes, which are of course for roles.

This was working as we were able to commit a role as recently as August 5th 2021 (can't say exactly what version I was on at the time but it was definitely 7.x).

Currently running SQL Source Control 7.2.35.13078. Have also tried uninstalling ALL Red Gate tools and clearing the AppData Local and Roaming folders (except for SQL Prompt 10 since I really didn't want to loose all my configurations), and then reinstalling.

This is happening on my coworker's computers as well, also on the latest version.

Attached are the comparison options (saved as .txt) which have not changed in almost a year of this project.

Jon_Kirkwood

Hi there,

Thanks for reaching out on the Redgate Forums and sorry to hear you are having issues with SQL Source Control

First thoughts are perhaps the roles have been filtered out to not appear in Commit & Get Latest tabs.

Can you please check the following:

SQL Source Control Setup > Edit filter rules > Role 

• Is it enabled?

• Are there any exclusions configured that may be leading to the behaviour you are experiencing

If the Role object type is enabled and there are no exceptions, the next step would be to compare the database & repository.

It would need an SQL Compare snapshot of the database & a zipped copy of the repository.

Use SQL Compare to compare the snapshot of your live dB against the copy of the repo to see if the Roles do appear. 

That would help start to isolate the location of the issue; specific to SQL Compare or to some of your own settings/filters.

Please reply to this post after checking the roles and we can assist further.

ejenkins

Thanks Jon! Yes we have the Filters check for Include All Roles enabled. However your suggestion to use SQL Compare of the database to SC helped narrow it down to the exact filter that was causing all roles, users and schemas to be "ignored". Here's a screenshot with the offending rule circled in red:



In summary we wanted to exclude all objects that belong to the tmp schema except for a table named PolMgrLoad. After removing the filter rule for "AND Object name Does not equal PolMgrLoad" roles and users appeared again in both SQL Compare and the Commit and Get Latest tabs! However this does mean we're no longer tracking changes for this one table we wanted to in our otherwise ignored tmp schema, but we can live with that.

Seems to me as if that inner/sub AND conditional is correctly applied to many objects such as tables, functions, etc., but not others such as users, roles and schemas.

Jon_Kirkwood

Looking like a move forward, thanks for the details and looks like you have a bit of a workaround to get moving.

I will work with our team to replicate the logic you have in place and see how those sub/nested AND statements are functioning and verify your statement about it not applying to users/roles/schemas.