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

Ignore Login when adding User SQL Azure

pauljoycepauljoyce Posts: 3 New member
edited June 7, 2023 9:00AM in SQL Compare
Hi,
I am using SQL Compare to compare 2 SQL Azure schemas. 
We have a DB per tenant on the one SQL Azure

When adding a User to Tenant DB SQL Compare adds a check for the related Login on the master DB
SQL Azure does not support this cross DB query. 

Is there any way to get SQL Compare to ignore the Login check? We add the login by hand to the master DB and then update all the Tenant DBs using the diff generated by SQL compare. 

e.g. the following will generate an error when run on SQL Axure 

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'RemoteControlService') CREATE LOGIN [RemoteControlService] WITH PASSWORD = 'p@ssw0rd' GO CREATE USER [RemoteControlService] FOR LOGIN [RemoteControlService] GO

Ideally, I would want 

DROP USER IF EXISTS RemoteControlService
CREATE USER RemoteControlService FOR LOGIN RemoteControlService WITH DEFAULT_SCHEMA=[dbo]


Tagged:

Answers

  • Options
    Ben_PBen_P Posts: 226 Silver 2
    Hi @pauljoyce thank you for your post.

    I believe the only way to achieve this would be to exclude Users with the use of Filters in SQL Compare:


    Then you would also need to uncheck the "ignore permissions" Option:





  • Options
    pauljoycepauljoyce Posts: 3 New member
    Hi Ben, 
    thanks for reply. 
    we need to include users in the compare. 
    just the related login that we want to exclude. 
    If this is the only way then we will need to remove the problem lines from the compare diff manually. Not ideal. 
  • Options
    Ben_PBen_P Posts: 226 Silver 2
    Hi Paul,

    You may be able to still include Users if you don't uncheck the users from the Filters list.

    The use of Filters will stop results showing in the upper results pane (after running the comparison) SQL Compare will still compare them, just not show the results.  
  • Options
    pauljoycepauljoyce Posts: 3 New member
    thanks. can this be done via command line?
  • Options
    Ben_PBen_P Posts: 226 Silver 2
    Hi Paul,

    Yes this can be done via the command line. To set the ignore permissions option you'd use the following switch:

    /Options:IgnorePermissions

Sign In or Register to comment.