Automated way to find all instances of previous SharePoint administrator

I am the new SharePoint admin for a fairly large implementation. The previous admin is gone, but his account and email are everywhere within SharePoint. I've coverd the obvious ones like the the admin in Central Administration, local admin on the boxes and such, but is there a way to find/replace his account info with mine everywhere it exists in SharePoint? I hate to spend the time going through each of the hundreds of site collections manually.
LVL 3
orbhotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

quihongCommented:
Is the previous admin still working at the company? What's your overall goal? To make sure you have the appropriate access you need and/or to assure the previous admin no longer have access?

There is no out of box way to "replace" an account with another one. However, it would be possible to script something or there are third party Administration tools that has this feature.

0
orbhotAuthor Commented:
He is gone. My goal is to replace every instance of his user name and email with mine, or a service account where approriate to that notifications and such are not going to a dead inbox (and no, I don't want the Exchange admins to fwd all his email to me... who knows what kind of junk he gets).

Any ideas how to script this replacement? I am a DBA before SharePoint admin and am decent at T-SQL... Is this data (the previous admin info) stored in the SharePoint config DB?
0
quihongCommented:
What notification are you referring to?

You're not going to be able to do this via SQL. Reading and writing to the SharePoint databases is not supported.

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

orbhotAuthor Commented:
Anything that is sent to the site collection admin, which the previous SP admin is set as all over the place on multiple site collections and in sites within those collections. If there is no automated way to change him to me, then I best get busy... lots of places to change.
0
quihongCommented:
Not sure about SP Alerts (Alert Me Feature), but here is sample code to change the Access Request Email

http://stabilissolutions.blogspot.com/2009/09/sharepoint-2007-request-access-feature.html

Use Powershell, check for his email and update it to yours.
0
orbhotAuthor Commented:
Nice find. Access requests really should go to the site collection owners, which is not the old SP admin in all cases, but with this script I can change just the ones that point to his email.
That solves one piece of the puzzle. I'll increase the point value and "accept multiple solutions" if I get another piece of the puzzle.
0
quihongCommented:
Whats the other piece of the puzzle? 250 points isn't much :)
0
quihongCommented:
Use the trial version of this - http://store.bamboosolutions.com/ps-74-5-alerts-administrator.aspx to figure out he Alerts.


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
orbhotAuthor Commented:
Hmm, that script isn't working for me. The links are broken and my script (attached) from the example on the page returns:

Missing variable name after foreach.
At E:\Scripts\ChangeRequest.ps1:2 char:9
+ foreach(S <<<< PWeb web in site.AllWebs)


SPSite site = new SPSite("http://amportal");
foreach(SPWeb web in site.AllWebs)
	{
		if (web.RequestAccessEnabled && web.RequestAccessEmail == "cmason@atlanticmarine.com)
		{
			web.RequestAccessEmail = "sharepoint@atlanticmarine.com";
		}
		web.Dispose();
	}
site.Dispose();

Open in new window

0
quihongCommented:
That code is generic .NET code, not Powershell.

PowerShell variables start with '$'.

I don't have the time to convert it for you. Sorry.
0
quihongCommented:
Powershell Code below to List out all the AccessRequestEmail. It should give you a good start.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null

$logFile = "C:\scripts\SharePoint\ListAccessRequestEmail.csv"

$startime = Get-Date
"Script Starting..." + $startime | Out-File -filepath $logFile

# Create the stopwatch
[System.Diagnostics.Stopwatch] $sw;
$sw = New-Object System.Diagnostics.StopWatch
$sw.Start()

$siteCollectionURL = "http://itportal-stage"
$theSite = New-Object Microsoft.SharePoint.SPSite($SiteCollectionURL)
$webApp = $theSite.WebApplication

foreach($site in $webApp.Sites)
{
	foreach($web in $site.AllWebs)
	{
		if ($web.RequestAccessEnabled)
		{
			$web.Url + "`t" + $web.RequestAccessEmail | Out-File -filepath $logFile -append
		}
		else
		{
			$web.Url + "`tRequest Access Not Enabled" | Out-File -filepath $logFile -append
		}
		$web.Dispose()
	}
	$site.Dispose()
}

$sw.Stop()
$theSite.Dispose()

# Write the compact output to the screen
write-host "Time: ", $sw.Elapsed.ToString()

Open in new window

0
orbhotAuthor Commented:
Dang, that's a complex PS script to not really do any work, such as actually update the email... just lists it out it looks like.
I found out this CAN be done at the DB. I'll still give out some points for the effort.

"Here’s a SQL statement that will change all request email addresses. The email address is stored in the “webs” table in a field called “RequestAccessEmail”.

UPDATE Webs SET RequestAccessEmail = ‘<new email>’ WHERE RequestAccessEmail = ‘<old email>’

You need to run the query in each content database.
"
http://vettekerry.wordpress.com/2008/09/12/access-requests-mass-update/
0
orbhotAuthor Commented:
Only post stuff you KNOW works, don't waste people's time posting untested stuff you find out on the Web.
0
orbhotAuthor Commented:
Here is the REAL solution...


USE [master]
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @dbname sysname --database name  
DECLARE @varSQL VARCHAR(512) --SQL statement

DECLARE db_cursor CURSOR FOR --Set up cursor to go through all MOSS content DBs (use the apprpriate prefix/suffix)
SELECT [name] 
FROM master.dbo.sysdatabases 
WHERE [name] LIKE 'MOSS_%_Content' 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @dbname   

WHILE @@FETCH_STATUS = 0   
BEGIN
	--SQL statement to update old admin's email with an in box that is monitored
	SET @varSQL = 'USE ' + @dbname + '; s
	UPDATE Webs 
	SET RequestAccessEmail = "newemail" 
	WHERE RequestAccessEmail = "oldemail";'
	EXEC (@varSQL)

	FETCH NEXT FROM db_cursor INTO @dbname   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

GO

Open in new window

0
quihongCommented:
Yes, the script I provided was to list out the AccessRequestEmail. It would only be a couple more lines to update the email.

Thanks for providing the SQL. Reading and writing to the SQL DB isn't supported by MS, so I stay away from it, especially if its possible via the Object Model.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.