Red Gate forums :: View topic - Excluding windows users from script
Return to www.red-gate.com RSS Feed Available

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

Excluding windows users from script

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Wed May 14, 2008 4:51 am    Post subject: Excluding windows users from script Reply with quote

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

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



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Wed May 14, 2008 11:19 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Wed May 14, 2008 1:12 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Mon May 19, 2008 4:23 am    Post subject: Reply with quote

Ok I'm trying something like this:

Code:

               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

Code:

//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?
Back to top
View user's profile Send private message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Wed May 21, 2008 9:05 am    Post subject: Reply with quote

Any suggetsions?
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 528
Location: Red Gate Software

PostPosted: Wed May 21, 2008 11:16 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
tchaplin@miningis.com.au



Joined: 23 Apr 2008
Posts: 15

PostPosted: Thu May 22, 2008 9:18 am    Post subject: Reply with quote

Ok thanks.
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