And you said you are unconsolable…clean up before you leave

Greetings,

I am approaching my last day at my current job. I love it here and will be really sad to leave, but have an awesome opportunity to grow my knowledge and career with a company on my “want to work for” list.

There are a lot of things to take care of before I leave.  I have been updating documentation (with meme’s) so that it is useful and fun.  I am trying to wrap up all my tickets and outstanding items and last night I woke up and realized, I was the owner of some databases.  This is how I fixed it:

I launched a query window on my Central Management Server to save time, but you can run this on one server at a time if you want.  I used the syntax from sp_helpdb to find out what I wanted to query:

select name, isnull(suser_sname(sid),'~~UNKNOWN~~') AS Owner, convert(nvarchar(11), crdate),dbid, cmptlevel
from master.dbo.sysdatabases
WHERE suser_sname(sid) = 'domain\MyUserName'

Some of the applications in my environment run under a special user and I didn’t want to interfere with those, I just wanted to fix the ones that use me.  Then I borrowed some code from Brent Ozar:

SELECT 'ALTER AUTHORIZATION ON DATABASE:: ['+ name +'] to sa;'
FROM master.dbo.sysdatabases
WHERE suser_sname(sid) = 'domain\MyUserName'

Here’s one I run on the CMS to find any SQL Agent Jobs that I own across my enterprise and then I can run the update scripts that are generated on the individual servers.


SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + [Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)+';'
FROM msdb.dbo.sysjobs
WHERE SUSER_SNAME(owner_sid) = 'domain\MyUserName'

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s