Want to ignore users...but it isn't

[douglaswilliams]

Hello…

We are having a problem when running a SQL Compare process (using the SDK in a VB.Net app).

We are using SQL Compare 7.

The target database is SQL 2005.

Our issue occurs when we attempt to synchronize a database with a set of scripts and there is task where the process attempts to drop users. We are not coding this specifically, so I am trying to understand where the DROP USER XXXX is coming from.

In our SQLCompare.Engine.Options I have included what I thought would make the process ignore the users:

Private _myCompareOptions As RedGate.SQLCompare.Engine.Options = _
Options.DoNotOutputCommentHeader Or _
Options.IgnoreWhiteSpace Or _
Options.IgnoreComments Or _
Options.IgnoreUsers Or _
Options.IgnoreFillFactor Or _
Options.IgnoreFileGroups Or _
Options.IgnoreUserProperties Or _
Options.IgnoreWithElementOrder Or _
Options.IgnoreSynonymDependencies Or _
Options.IgnoreSchemaObjectAuthorization Or _
Options.IgnoreOwners Or _
Options.IgnorePermissions Or _
Options.IgnoreBindings

The issue is occurring because there is an orphaned SQL Login User in a customer database that actually owns a schema.

We see the following messages:

(1)Dropping a user is a potentially dangerous operation. Dependencies for the bob user are not selected automatically. If you are sure you want to drop this user please select the objects that belong to this user manually.
The user bob does not have an associated login. If the user was not defined with WITHOUT LOGIN then the target database is in an inconsistent state. Please fix this by using sp_change_users_login.

(2)The database principal owns a schema in the database, and cannot be dropped.

I understand that the user cannot be dropped until the schema owner is changed. But I am trying to get the sql compare to ignore any users and just leave them. Is this possible?

Thanks,
Douglas

[Brian Donahue]

Hi Douglas,

I believe you have to set the "Include" property on each Difference that is of type "User" for this to happen, pretty much the same procedure as excluding any other type of difference. The only atomic option regarding users is to ignore the user properties, which works to limit some of the ways in which a user will come up a "different" in SQL 2005 and 2008.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[douglaswilliams]

Brian...

thanks for the speedy reply.

However, i am not sure what you mean.

Our situation is that there is an orphaned SQL Login User that was set to own a schema. This user does not exist in the database we're using as the base. The Drop User is, naturally, failing because there is a dependency between the user and schema. What we want to do is ignore this dependency...or, better yet, to ignore the users and not even drop them. i thought with the options we set that would work...but apparently the options are not intuitive to me.

Can you clarify what you meant as far as changes that we'd need to make in our code? Or is it just not possible?

thanks,
- douglas

[douglaswilliams]

Brian...

Okay, I think figured this out based on what you wrote.

I changed our code to exclude the Difference DatabaseObjectType where it is a User. This definitely ignores any users.

Thanks,
- douglas

[Brian Donahue]

Hi Douglas,

That should do the trick!

[douglaswilliams]

Hi Brian,

yes, that is what i did and it worked perfectly.

thanks
- douglas