/**************************************************
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 !!!'
Select Help,good_advice
From sys.dm_db_good_performance
These postings are provided "AS IS" with no warranties.
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.
Friday, February 27, 2015
Simple Tools Series: Change the owner of all Jobs 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 Jobs to the SA login
Author: Rasmus Glibstrup
Company: it-Craft A/S, Denmark
Date: 25-FEB-2015
Version: 1.0
Script to change all jobs to be owned by the SA
login. This usually prevents errors when AD accounts
are disabled - eg. consultants, old emplóyees ect.
The script works for:
- SQL Server 2014
- SQL Server 2012
- SQL Server 2008 R2
- SQL Server 2008
- SQL Server 2005
***************************************************/
USE [msdb]
GO
--Variables
Declare @JobID uniqueidentifier, @JobName sysname
--Cursor to detect and change the jobs not owned by the SA login
Declare JobCursor CURSOR FOR
Select job_id, name
from sysjobs
where SUSER_SID('sa')<> owner_sid
OPEN JobCursor
FETCH NEXT from JobCursor into @JobID, @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
--Print the Job name
PRINT 'JOB: [' + @Jobname + '] - is changed to SA owner'
--Execute the change, comment the next line if you just want to see the jobs with an non SA login owner
EXEC msdb.dbo.sp_update_job @job_id=@JobID, @owner_login_name=N'sa'
FETCH NEXT from JobCursor into @JobID, @JobName
END
CLOSE JobCursor
DEALLOCATE JobCursor
Subscribe to:
Posts (Atom)