Header

Friday, March 13, 2015

Simple Tools Series: Change the owner of all Databases to the SA login

"Simple Tools Series" - Is a selection of simple every day scripts, that any DBA will need either on a daily basis or frequently.



/**************************************************
Change the owner of all Databases to the SA login

Author:  Rasmus Glibstrup
Company: it-Craft A/S, Denmark
Date:  25-FEB-2015
Version: 1.0

Script to change all databases to be owned by the SA 
login. This usually prevents errors when AD accounts
are disabled - eg. consultants, old employees ect.

The script works for:
SQL Server 2014
SQL Server 2012
SQL Server 2008 R2
SQL Server 2008
SQL Server 2005
***************************************************/
USE [master]
GO

--Variables
Declare @DBname sysname, @Command nvarchar(max), @Count int
SET @Count = 0
--Cursor to detect and change the databases not owned by the SA login
Declare DBCursor CURSOR FOR
Select name 
from sys.databases
where SUSER_SID('sa')<> owner_sid  

OPEN DBCursor
FETCH NEXT from DBCursor into @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @Count +=1

 --Print the database name
 PRINT 'DATABASE: [' + @DBName + '] - is changed to SA owner'

        SET @Command = 'ALTER AUTHORIZATION ON DATABASE::[' + @DBname + '] TO sa'

 --Execute the change, comment the next line if you just want to see the jobs with an non SA login owner
 exec (@Command)

 FETCH NEXT from DBCursor into @DBName
END
CLOSE DBCursor
DEALLOCATE DBCursor

IF @Count = 0
PRINT '!!! All databases is owned by the SA login !!!'