Force Check-in Stored Procedures

jeffeff

We recently migrated a database from an Azure virtual server to an Azure Managed Instance.  The authentication model is slightly different between the two.

In the first, the GRANTs are of the form:
GRANT EXECUTE ON [dbo].[REPORT_ItemList] TO [DOMAIN\D-GroupName]

whereas in the second they are of the form:
GRANT EXECUTE ON [dbo].[REPORT_ItemList] TO [D-GroupName]

The problem is that SQL Source Control does not consider this to be a difference so I can't check the stored procs in with the new GRANTs.

Is there an option that I can change or some other method that I can use to force the check-in of these procs?

Any assistance would be much appreciated :-)

DanC

Hi @jeffeff

Could I ask what version of SQL Source Control you're running?

jeffeff

We are running version 7.2.46.13264 - I have not been updating SQL Source Control for a few weeks since a colleague of mine had issues with SQL Source Control not playing nicely in our new Azure environment.

DanC

Hi @jeffeff

Thanks for confirming that for me, in terms of the options used in SQL Source Control, are you using ignore "Permissions"?

Also in order for me to try and reproduce this issue, can I ask what type of object "REPORT_ItemList" is?

jeffeff

Hi Dan

The ignore Permissions option is currently not checked but I have tried both ways.

REPORT_ItemList is TSQL stored procedure.

Thanks for your efforts on this - let me know if you have any further questions

Jeff :-)

DanC

Hi @jeffeff

Thanks for the information there!

I've managed to reproduce the issue you're experiencing, for added info it's coming from the comparison engine (SQL Compare) which is behind the scenes for SQL Source Control. 

I'm going to escalate this to the devs and I'll keep you posted on any updates!

DanC

Hi @jeffeff

As a follow-up from the devs, can I ask if you're using "Ignore user properties" and whether this changes the behavior?

jeffeff

Hi Dan

I have tried various combinations of:
 Ignore Permissions
 Ignore Users' permissions and role memberships
 Ignore User properties

Ignore User Properties does not seem to have any effect.  I currently have all of the Ignore switches unchecked and it is not detecting the differences.

jeffeff

I assume that this item has fizzled out?

DanC

Hi @jeffeff

Apologies, this is still being investigated, as you didn't tag me it slipped through the cracks, so apologies for that!

I'll pass the details onto the devs and keep you posted!

DanC

Hi @jeffeff

I'm wondering if it's worth investigating this further within a support ticket to keep track of this. 

The developers have done some testing and found when using the engine behind SQL Source Control (SQL Compare) it highlights the differences between tho grants and allows the changes to be made. 

In terms of SQL Source Control from your perspective, it's not making this comparison and so you can't commit those changes, is that right?