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'