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.

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!

Differences diffs = refDb.CompareWith(targetDB, options); 
            foreach (Difference d in diffs)
            {
                if (d.DatabaseObjectType == ObjectType.User || d.DatabaseObjectType == ObjectType.Role) d.Selected = false;
                else d.Selected = true;
            }

douglaswilliams

Hi Brian,

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

thanks
- douglas