Delete Orphaned AD Users from the Site Collection

Recently I was working on the packaging up the site collection developed in my virtual machine and deploying to the client’s environment. In my virtual environment, I have created small sampling of client users in my active directory (e.g. Niks\tpatel) during the development and demo process. During the deployment, when I packaged up the site collection using the Backup-SPSite command and restored in the client environment using the Restore-SPSite command, everything looked great except people picker controls and security management pages showed the source active directory users (e.g. Niks\tpatel). What it means is there are duplicate users from my active directory and client’s active directory causing confusion among user identity (e.g. Niks\tpatel and ClientAD\tpatel). This is really interesting because as I am researching this issue, this has been known issue since WSS 2.0 era (see the links below) and I have never came across this situation in last 5 years. May be I have never noticed enough.

In my sample use case, I have a “Niks\tpatel” user added to the site membership in my virtual environment. After deploying the site collection in the client environment, I am able to browse the “Niks\tpatel” user from the target environment’s people picker control.

To gain more insight into where would people picker control show the user information from, I have launched extensive research on web and gladly found many resources but none of them captured in one article. This article summarizes what I have learned over the last couple of days and how to clean up the orphaned AD users from the site collection or cleanup the users from the site collection before getting ready for the packaging up for the deployment.

As we all know, SharePoint Foundation or WSS provides wonderful Active Directory integration from out of the box but many of us don’t know, behind the screen, WSS acquire user’s login name, display name, and email address whenever they are added to the site membership and saves them to the UserInfo table in the content database of the given site collection\web application. Whenever user creates or modifies the SharePoint list items, these users will linked to the list item and shows up in the “Created By” and “Modified By” indicators.

What it means is People Picker will show the combined users view from the authentication provider source (e.g. LDAP accounts from the Windows Authentication/Active Directory) and the accounts from the User Information List (UserInfo table in WSS_Content DB of the Site Collection/web application). This is important to understand as it means that accounts that may have been deleted or disabled or not exists in your authentication provider still show up because he may exists in the User Information List. In our scenario, this is where orphaned AD user (Niks\tpatel) shows up in the people picker control because even though she is not available in the target environment’s active directory but lingered in the UserInfo table from the source site collection.

To view the User Information List from the browser, log in to the site using the Administrator privileges, and navigate to the /_catalogs/users/simple.aspx. This page will show all the active users from the User Information List. Please note that you can’t delete the users from User Information List using the browser interface.

Additionally, you can browse the full User Information List by accessing the UserInfo table from the site collection\web application content database. As you can see from the folllowing screen, only three users are marked as active. Please note that you should not make any direct modification to the database or delete users from the UserInfo table. As you can see from the following screen, Niks\tpatel is still active user and exists in the UserInfo table even though she is either deleted from the active directory or not exists in the target active directory.

Now, since we know UserInfo table is culprit to show the user in the people picker, how would you clean up the orphaned AD users from the User Information List?

The best approach to delete the orphaned AD users from the User Information List, you should write a custom code and remove the user using the SPWeb.SiteUsers API. This will delete the users from the top level site collection. If you are inheriting the site membership in the sub sites, deleting users from the top level site will delete the users from the sub site making sure deleted users are not available in the sub site’s people picker. If you have broken the site membership inheritance, you have to manually write a extra code to delete the users using the SPWeb.Users API.

Step 1: To delete the specific user, Open the Visual Studio 2010 and create the Console Application, Target the project to Microsoft.NET 3.5 framework and “Any CPU” build. Please be careful with this step because Visual Studio 2010 defaults to Microsoft.NET Framework 4.0 and 32-bit runtime. Reference the Microsoft.SharePoint.dll in the console application, Import the Microsoft.SharePoint.dll in the class, and copy the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace DeleteSPUserInfoListUser
{
    class Program
    {
        static void Main(string[] args)
        {

            string loginName = @”Niks\tpatel”;  //@”Niks\Administrator
            using (SPSite spSite = new SPSite(“http://sp2010vm“))
            {
                using (SPWeb spWeb = spSite.OpenWeb())
                {
                    SPUser spUser = spWeb.SiteUsers[loginName];
                    if (!spUser.IsDomainGroup &&
                        !spUser.IsSiteAdmin &&
                        !loginName.Equals(@”NT AUTHORITY\authenticated users”) &&
                        !loginName.Equals(@”NT AUTHORITY\LOCAL SERVICE”) &&
                        !loginName.Equals(@”SHAREPOINT\system”))
                    {
                        spWeb.SiteUsers.Remove(spUser.LoginName);
                        spWeb.Update();

                        Console.WriteLine(“User Deleted – ” + loginName);
                    }
                }
            }

            Console.WriteLine(“Press any key…”);
            Console.Read();
        }
    }
}

Step 2: Modify the code by replacing your Site URL and User Name. You can extend this code to delete all the orphaned users by looping through the collection.

Step 3: Run the console Application and verify that User is deleted and program runs successfully.

Step 4: Access the UserInfo table from the site collection\web application content database and verify that user is flagged as deleted and she is no longer active. Alternatively, you can access the /_catalogs/users/simple.aspx to verify that user isn’t exists in the User Information List.

Step 5: Verify that deleted user (e.g. Niks\tpatel) is not browsable in the People Picker control.

That’s it. Hopefully this will be helpful to someone who is looking for both background story and sample code to clean up the site collection users.

At last, here are some of the resources helped me to clean up the users from the site collection. Please review these resources for more details.

Andrew Connell’s thoughts on the UserInfo and Profile DBs and how they work in the WSS 2.0. Most of these are still valid in the SharePoint 2010.
http://www.andrewconnell.com/blog/archive/2005/08/18/1912.aspx

Sahil Malik and User Profiles in the MOSS 2007
http://blah.winsmarts.com/2007-7-SharePoint_2007__All_you_ever_wanted_to_know_about_User_Profiles.aspx

Tobias Zimmergren and User Information List
http://www.zimmergren.net/archive/2008/06/25/sharepoints-hidden-user-list-user-information-list.aspx

Step by Step Profile DB Cleanup
http://blogs.technet.com/b/seanearp/archive/2009/03/04/sharepoint-profile-cleanup.aspx

SharePoint Automation – Sample code to delete all the users from the site collection. There is nice enhancement in the comments section to lookup the deleted or disabled AD users.
http://stsadm.blogspot.com/2008/08/delete-all-users-from-site-collection.html

About these ads
This entry was posted in SP2010 Admin General. Bookmark the permalink.