Red Gate forums :: View topic - Want to ignore users...but it isn't
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK Previous Versions
SQL Comparison SDK Previous Versions forum

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

Search in SQL Comparison SDK Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
douglaswilliams



Joined: 05 Jun 2009
Posts: 4

PostPosted: Fri Jun 05, 2009 5:00 pm    Post subject: Want to ignore users...but it isn't Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Fri Jun 05, 2009 6:05 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
douglaswilliams



Joined: 05 Jun 2009
Posts: 4

PostPosted: Fri Jun 05, 2009 6:10 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
douglaswilliams



Joined: 05 Jun 2009
Posts: 4

PostPosted: Fri Jun 05, 2009 7:16 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Mon Jun 08, 2009 11:32 am    Post subject: Reply with quote

Hi Douglas,

That should do the trick!
Code:
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;
            }
Back to top
View user's profile Send private message
douglaswilliams



Joined: 05 Jun 2009
Posts: 4

PostPosted: Mon Jun 08, 2009 2:49 pm    Post subject: Reply with quote

Hi Brian,

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

thanks
- douglas
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group