Deleting users from all sites in SPS 2003

Deleting users from all sites in SPS 2003
By tpham
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.

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 Files\Common Files\Microsoft Shared\web server extensions\60\BIN\stsadm.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’DOMAINNAME\username1′ OR dbo.UserInfo.tp_Login = N’ DOMAINNAME\username2′) 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 DOMAINNAME\username1 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 “ | 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.