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 !!!'

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

Thursday, December 4, 2014

Microsoft Campus Days 2014

Links to slides and democode from the session "Performance Tuning for the Accidental DBA" held at Microsoft Campus Days 2014 can be found here: http://www.it-craft.dk/sqlevents/

Wednesday, April 30, 2014

Monday, March 31, 2014

VSS default settings prevent Integrity Check on large databases

Recently we encountered a customer, who got the following error while doing DBCC Checkdb /Integrity check on his large database.

Error: 17053, Severity: 16, State: 1.
"Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered"
OR 
Error: 17053, Severity: 16, State: 1.
"The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001447cd6000 in file 'X:\SqlDataFiles\LargeDB.ndf:MSSQL_DBCC45'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online."
The error in the dbcc checkdb is due to a default limitation in VSS, which DBCC is using to complete a full database consistency check in the background.
By default a VSS snapshop may not be larger than 10% of the size of the drive. This can be changed be going to Computer Management -> Disk Management -> Select Properties on all the drives that contains the database -> Select the pane Shadow Copies" -> Select Settings -> Select the redio button "No limit"



This removes the VSS limitation that causes the Error 655 

Wednesday, March 26, 2014

Monitoring if new databases are created

Want an email when somebody is creating a new database on your SQL Server ?

--- CREATE TRIGGER ON DATABASE CREATION
CREATE TRIGGER DDL_TRIG_CREATE_DATABASE
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
 DECLARE @ServerName nvarchar(255)
 DECLARE @Cmd nvarchar(max)
 DECLARE @DBName nvarchar(255)
 DECLARE @Subj nvarchar(2000)
 DECLARE @XML XML
 DECLARE @Recipients nvarchar(255)
 DECLARE @MailProfile nvarchar(255)

 SET @Recipients = 'you@yourcomapny.dk'
 SET @XML = EVENTDATA()
 SET @Cmd = CAST(@XML as nvarchar(MAX))
 SET @ServerName = @@SERVERNAME
 SET @DBName = @XML.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(200)') 
 SET @Subj = 'New database "' + @DBName + '" created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME()
 
 -- Find the database mail profile name
 create table #dbmail_profile
 (
  profileid int null,
  profilename varchar(125) null,
  accountid int null,
  accountname varchar(125) null,
  sequencenumber int null
 )
 insert #dbmail_profile
 EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

 select @MailProfile = profilename
 from #dbmail_profile
 where sequencenumber = 1

 drop table #dbmail_profile

 EXEC msdb.dbo.sp_send_dbmail
  @profile_name = @MailProfile,
  @recipients = @Recipients,
  @subject = @Subj,
  @body_format = 'TEXT',
  @body = @Cmd;

Wednesday, February 26, 2014

Which databases is this login used in ?

When moving databases and logins, or just cleaning the server of unused logins this script can help you test if a single login is mapped to any databases.

-- =============================================
-- Author: Rasmus Glibstrup, SQLGuyDK
-- Company: it-Craft
-- Create date: 26. Feb. 2014
--
-- Description:   
-- Which databases are a single login mapped to ?
--
-- When moving databases and logins, or just cleaning
-- the logins of the server this script can show you
-- which databases a specific login is mapped to.
--
--
-- DISCLAIMER !
-- THIS CODE-SAMPLE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED  
-- OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR  
-- FITNESS FOR A PARTICULAR PURPOSE. 
-- =============================================

-- Change SQLGuy to the login you need to find
DECLARE @LoginName nvarchar(1000) = 'SQLGuy'


DECLARE @SqlStm nvarchar(1000)
Create table #LoginTable(LoginMappedInDatabase nvarchar(1000),LoginName nvarchar(1000),DatabaseUser nvarchar(1000))

DECLARE @DBName nvarchar(1000)
DECLARE dbCursor CURSOR 
FOR SELECT name from sys.databases
Where state_desc = 'online'

OPEN dbCursor

FETCH NEXT FROM dbCursor into @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SqlStm ='INSERT INTO #LoginTable Select ''' + @DBName + ''' as LoginMappedInDatabase,SP.name as LoginName, DP.name as DatabaseUser from sys.server_principals SP inner join [' + @DBName + '].sys.database_principals DP ON SP.sid = DP.sid Where SP.Name=''' + @LoginName + ''''
 
 exec (@SqlStm)
 FETCH NEXT FROM dbCursor into @DBName
END

CLOSE dbCursor
DEALLOCATE dbCursor

Select *
FROM #LoginTable
Drop table #LoginTable