Deleting users from all sites in SPS 2003


One of the frustrating things for administrators working with SharePoint Portal Server 2003 is managing “dead users”.  If an employee leaves the company for example, you can easily remove their account from the SharePoint profile database.  However, that does not remove their access to any WSS Sites where they have been added as a member.  They can’t access it of course if their Active Directory account is gone, but they’ll still be listed in the Manage Users page or any Site Members Web Parts.  Sure, you can use the stsadm.exe tool with the “deleteuser” command, but you have to do that with *every* site which that user is a member of.  And sure, you can use the “enumusers” switch to find these dead users, but again that is limited to listing the users for one site only.  If you manage an environment with dozens or hundreds of sites, this can be tedious indeed.

There are some tools out there to help manage these issues though:

SharePoint User Manager 2003:
http://blogs.msdn.com/nigelbridport/archive/2004/12/16/317419.aspx

SharePoint Utility Suite 2.5:
http://www.microsoft.com/sharepoint/downloads/components/detail.asp?a1=724

The former is nice for browsing memberships per-site, and the latter has lots of uses but I couldn’t figure out if it could manage dead users easily.  So I decided to “extend” the stsadm tool by generating script commands to delete specified users from all sites that they are a member of.  To do this requires some SQL and the standard disclaimer that ALL MODIFICATIONS TO THE SHAREPOINT DATABASE ARE UNSUPPORTED!  So be careful, and be assured that I’m just doing some reads here.

So, fire up SQL Query Analyzer, connect to your SharePoint SQL database, and switch the active database to PORTALNAME_SITE.  Then paste the following SQL:

SELECT DISTINCT
N'”C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60BINstsadm.exe”‘+
N’ -o deleteuser -url “
http://portaladdress/’ + dbo.Webs.FullUrl +
N'” -userlogin “‘ + dbo.UserInfo.tp_Login + N'”‘ AS Command
FROM dbo.Webs RIGHT OUTER JOIN dbo.UserInfo ON dbo.Webs.SiteId = dbo.UserInfo.tp_SiteID
WHERE
(dbo.UserInfo.tp_Login = N’DOMAINNAMEusername1′ OR
dbo.UserInfo.tp_Login = N’ DOMAINNAMEusername2′) AND
(dbo.Webs.FullUrl IS NOT NULL) AND (dbo.Webs.FullUrl LIKE N’sites%’)

Of course, change the path to stsadm.exe if it is different for your server, replace http://portaladdress with your root portal address, and replace DOMAINNAMEusername1 with the accounts that you want to remove.  Then run the query, and it will generate one “stsadm –o deleteuser” command for every user and for every site which that user is a member of.  Copy the output to the clipboard, paste it into Notepad and save it as a .bat file.  Then run the .bat file in the Command Window using “<filename> | clip”.  The output will be silent but captured to the clipboard so you can review any possible errors after the operation is completed.  Some errors that may occur include deleting the last administrator of a Site, missing users (I got lots of these but don’t know why yet) and deleting users from sub-sites in a Site collection that does not have unique permissions.  All of these errors are harmless, but you’ll be left with a much cleaner collection of Site memberships in SharePoint, free of those dratted ex-employees whom you want to forget about and can now safely do so since their name won’t keep popping up anymore. :-)


View article…

Eric Legault

Microsoft Outlook MVP (15 yrs). Author: Microsoft Office 2013 Pro Step By Step. Consultant/speaker/Outlook add-in builder/SharePoint pioneer. Guitarist: Maiden Canada (Iron Maiden tribute band)

You may also like...

%d bloggers like this: