What are the challenges you face when working across database platforms? Take the survey
Options

Force Check-in Stored Procedures

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 :-)

Answers

  • Options
    Hi @jeffeff

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

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    jeffeffjeffeff Posts: 5 New member
    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.
  • Options
    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?



    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    jeffeffjeffeff Posts: 5 New member
    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 :-)
  • Options
    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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    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?

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    jeffeffjeffeff Posts: 5 New member
    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.

  • Options
    jeffeffjeffeff Posts: 5 New member
    I assume that this item has fizzled out?
  • Options
    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!

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    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?

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

Sign In or Register to comment.