Ignore Login when adding User SQL Azure

pauljoyce

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]

Ben_P

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:

pauljoyce

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. 

Ben_P

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.  

pauljoyce

thanks. can this be done via command line?

Ben_P

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