Excluding windows users from script

tchaplin@miningis.com.au

I am trying to not include windows users in my scripting app. I am using the following code.

foreach (Difference difference in differences)
{
  difference.Selected = true;
  if (difference.DatabaseObjectType == ObjectType.User)
  {
    RedGate.SQLCompare.Engine.User u = (RedGate.SQLCompare.Engine.User) difference.ObjectIn1;
    if(u.UserType == UserType.WindowsUser)
    {
      difference.Selected = false;
    }						
}

But they still appear in the differences script.

I think this is because they have been granted permissions on objects that I am including, like store procedures.

How do make it so the windows aren't included, no matter permissions they have?

Michelle T

You need to remove Options.IncludeDependencies from your Options when you synchronize the databases.

If you're using Options.Default, instead use Options.Default ^ Options.IncludeDependencies.

You'll have to make sure that you've selected dependent objects yourself, though, and if the users are actually needed and not present then the script might not run.

You might also want to use Options.IgnoreUsers (which is actually short for 'ignore users' permissions and role memberships' - it will not attempt to synchronize permissions and role memberships held by users, which might help the script run smoother) and Options.IgnorePermissions (which will not synchronize any permissions) on both comparison and synchronization, if you don't need to synchronize other occasions where those things appear.

tchaplin@miningis.com.au

Hmm when I use Options.Default ^ Options.IncludeDependencies it excludes the users now but they are still included in the roles, permissions and schemas, which obviously causes a problem.

I can add Options.IgnoreUsers and Options.IgnorePermissions (I haven't found one for schemas) but then *none* of the role memberships or permissions are included. I would like the role memberships, permissions and schemas for Sql users to be included.

Is there way to exclude Windows users and all their dependant objects, whilst leaving all Sql users with their roles, permisions and schemas intact?

For your information, I am actually comparing the database against null to get a database create script - I am not interested in synchronizing 2 databases at this stage. Basically I'm bring back scripts from a client's office and rebuilding them in my office - and so I can't include Windows from another domain. Is there another way around it? Can I convert Windows users to Sql users?

tchaplin@miningis.com.au

Ok I'm trying something like this:

					foreach (Difference difference in differences)
					{
						difference.Selected = true;						
						//Ignore Windows users if required.						
						if (!includeWindowsUsers)
						{
							//Deselect this object if it's owner is a windows user.
							difference.Selected = (differences.Database1.Users[difference.ObjectIn1.Owner].UserType != UserType.WindowsUser);							
							//Deselect this object if it is a windows user.
							if (difference.DatabaseObjectType == ObjectType.User)
							{
								RedGate.SQLCompare.Engine.User u = (RedGate.SQLCompare.Engine.User)difference.ObjectIn1;
								if (u.UserType == UserType.WindowsUser)
									difference.Selected = false;
							}							
						}			
					}

Then I was thinking of adding on code to remove users from roles if they are windows users, e.g. add this onto the end of the if block

//Deselect windows users from roles
							else if (difference.DatabaseObjectType == ObjectType.Role)
							{
							    RedGate.SQLCompare.Engine.Role r = (RedGate.SQLCompare.Engine.Role)difference.ObjectIn1;
							    foreach (IDatabaseObject dbo in r.Members)
							    {
									//Need to exclude memeber if it is a windows user
							    }
							}	

Except that it seems I can't remove users from a role here ...

Then there is also the matter of permissions, I need to be able to cycle through all database objects that have permissions (is there an interface for this?) and remove the user from the from the grant statement ... is that possible?

tchaplin@miningis.com.au

Any suggetsions?

Michelle T

There's no support for modifying the Database object to get bits of the script for objects excised except through the Options. The only way to really do what you're after right now is to find the actual strings in the actual script after generating it and remove them before running the script.

tchaplin@miningis.com.au

Ok thanks.