Part 1 -- Restore a database to another instance...

--############################################################################################################################
--
--This script is being offered for public use and as such is being offered as untested and unverified.
--Please use this script at your own risk, as I take NO responsibility for it's use elsewhere in environments 
--that are NOT under my control. 
--Redistribution or sale of kill_users_other_than_system_sp, in whole or in part, is prohibited! 
 
--Always ensure that you run such scripts in test prior to production and perform due diligence as to whether they meet yours, 
--or your company needs!
--
-- you will need to replace any item enclosed in <> with your own values!
--
--############################################################################################################################

USE [<your admin DB>]
GO
/****** Object:  StoredProcedure [<your admin schema>].[kill_users_other_than_system_sp]    Script Date: 04/22/2015 10:27:18 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [<your admin schema>].[kill_users_other_than_system_sp] 
--
--#############################################################
--
-- Author: Haden Kingsland
-- Date: 08/09/2010
-- Version: 01:00
--
-- Desc:To check for users other than myself and "sa" in a given (passed in)
--database. This is to be run prior to a restore to ensure that an exclusive lock
--can be taken on the database to do the restore.
--MUST exist in the MASTER DB of the receiving instance as remotely called from the
--restore_databases_to_another_instance_sp stored procedure on the source instance.
--
----#############################################################
--
-----------------------
-- Modification History
-----------------------
--
--#################################################################

(
@dbname sysname,
@p_error_description varchar(300) OUTPUT
)


AS

BEGIN

DECLARE @strSQL varchar(255),
@MailProfileName VARCHAR(50),
@spid varchar(10), 
@loginame varchar(255),
@program_name varchar(128),
@hostname varchar(20),
@MESSAGE_BODY varchar(2000),
@MESSAGE_BODY2 varchar(1000)

SELECT @MailProfileName = name
FROM msdb.dbo.sysmail_profile WITH (NOLOCK)
WHERE name like '%<your mail profile name>%'

PRINT 'Killing ' + UPPER(@dbname) + ' Database Connections'
PRINT '----------------------------------------------------'
DECLARE LoginCursor CURSOR READ_ONLY
for select spid, loginame, program_name, hostname from master..sysprocesses
where UPPER(cmd) not in (
'LAZY WRITER', 
'LOG WRITER', 
'SIGNAL HANDLER', 
'LOCK MONITOR', 
'TASK MANAGER', 
'RESOURCE MONITOR',
'CHECKPOINT SLEEP',
'CHECKPOINT',
'BRKR TASK',
'BRKR EVENT HNDLR',
'TRACE QUEUE TASK')
AND db_name(dbid) = db_name(db_id()) -- @DBNAME
AND hostname != '<your machine>' -- not my pc!
and loginame <> 'sa'

OPEN LoginCursor

FETCH NEXT FROM LoginCursor INTO @spid, @loginame, @program_name, @hostname

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

PRINT 'Killing user spid: ' + @spid + ' Name: ' + @loginame
SET @strSQL = 'KILL ' + @spid

BEGIN TRY

--set @output = ISNULL(@output,' ') + ' ' + @strsql
--print @output
--PRINT @strSQL

EXEC (@strSQL)
 
SET @MESSAGE_BODY = ' User: ' + @spid + ' ' + LTRIM(RTRIM(@loginame)) + ' was killed as part of the restore to database ... ' + @dbname + ' in instance ... ' + @@SERVERNAME + '. Program name: ' + LTRIM(RTRIM(@program_name)) + ' was running on host: ' + @hostname
SET @MESSAGE_BODY2 = ' User: ' + @spid + ' has been killed! in database ... ' + @dbname

EXEC msdb.dbo.sp_notify_operator 
@profile_name = @MailProfileName, 
@name=N'<your operator>',
@subject = @MESSAGE_BODY2, 
@body= @MESSAGE_BODY
 
END TRY

BEGIN CATCH
SELECT @p_error_description = ERROR_MESSAGE();
RETURN;
END CATCH

END

FETCH NEXT FROM LoginCursor INTO @spid, @loginame, @program_name, @hostname

END

CLOSE LoginCursor

DEALLOCATE LoginCursor

END
RETURN;