USE MASTER
GO

/*
This procedure substitutes xp_sendmail with a wrapper for sp_sendSAM. 
It preserves all the current permissions and generates a rollback script
that can be used to go back to xp_sendmail if you decide not to use SqlAnswersMail.
*/

set nocount on

if replace(@@Version,' ','') not like '%MicrosoftSQLServer2000%'
begin
	Raiserror( 'This script is only supported on SQL Server 2000', 16, 1)
	return
end

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xp_sendmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
	Raiserror( 'Xp_sendmail is already setup as a stored procedure. This script is designed to upgrade from xp_sendmail extended procedure to SqlAnswersMail. The script cannot proceed.', 16, 1)
	return
end

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sendSAM]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
	Raiserror( 'sp_sendSAM does not exist in the Master database. Please install SqlAnswersMail before running this script.', 16, 1)
	return
end


declare @perms VARCHAR(8000)
set @perms = ''

select @perms = @perms + char(13) + case protecttype 
	when 204 then 'GRANT EXECUTE on XP_SendMAIL to [' + u.name + '] WITH GRANT OPTION'
	when 205 then 'GRANT EXECUTE on XP_SendMAIL to [' + u.name + '] '
	when 206 then 'DENY EXECUTE on XP_SendMAIL to [' + u.name + ']' end
from sysprotects p join sysusers u on p.uid=u.uid 
where object_name(p.id)='xp_sendmail' and action=224

declare @dll VARCHAR(500)
select @dll = substring(c.text,1,255) from syscomments c join sysobjects o on c.id = o.id where name = 'xp_sendmail' and xtype = 'X'

IF @dll is not null
	exec sp_dropextendedproc xp_sendmail
else
	set @dll = 'sqlmap70.dll'


exec ('create proc dbo.xp_sendmail
		@recipients varchar(4000) = '''',
		@message ntext = null,
		@query varchar(7800) = null,
		@attachments varchar(4000) = null,
		@copy_recipients varchar(4000) = null,
		@blind_copy_recipients varchar(4000) = null,
		@subject varchar(4000) = ''SQL Server Message'',
		@type varchar(255) = null,
		@attach_results varchar(5) = FALSE,
		@no_output varchar(5) = FALSE,
		@no_header varchar(5) = FALSE,
		@width int = 0,
		@separator varchar(10) = null,
		@echo_error varchar(5) = FALSE,
		@set_user sysname = null,
		@dbuse sysname = null,
		@timeout int = 300,
		@sender_email varchar(200) = '''',
		@format varchar(10) = '''',
		@attachment_format varchar(10) = '''',
		@wrap_query varchar(5) = FALSE,
		@css_stylesheet varchar(8000) = '''',
		@call_mode varchar(10) = ''mail'',
		@pdf_definition varchar(8000) = '''',
		@xls_definition varchar(8000) = '''',
		@doc_definition varchar(8000) = '''',
		@one_per_recipient varchar(5) = FALSE,
		@zip_attachments varchar(5) = FALSE,
		@zip_definition varchar(8000) = '''',
		@queue varchar(100) = '''',
		@queue_definition varchar(8000) ='''',
		@content_path varchar(1000) = '''',
		@content_path_definition varchar(8000)='''',
		@merge_from_query int = 0,
		@merge_from_query_definition varchar(8000)='''',
		@ansi_attachment varchar(5)='''',
		@importance int=1,
		@reply_to varchar(4000) = '''',
		@email_when_empty varchar(10) = ''DEFAULT'',
		@exception_mode varchar(10) = ''DEFAULT'',
		@quote_all varchar(5) = TRUE,
		@mail_server varchar(100) = ''''
	as
		exec sp_sendSAM
			@recipients = @recipients,
			@message = @message,
			@query  = @query,
			@attachments = @attachments,
			@copy_recipients = @copy_recipients,
			@blind_copy_recipients  = @blind_copy_recipients,
			@subject = @subject,
			@type = @type,
			@attach_results = @attach_results,
			@no_output = @no_output,
			@no_header = @no_header,
			@width = @width,
			@separator = @separator,
			@echo_error = @echo_error,
			@set_user = @set_user,
			@dbuse = @dbuse,
			@timeout = @timeout,
			@sender_email  = @sender_email,
			@format = @format,
			@attachment_format = @attachment_format,
			@wrap_query = @wrap_query,
			@css_stylesheet = @css_stylesheet,
			@call_mode = @call_mode,
			@pdf_definition = @pdf_definition,
			@xls_definition = @xls_definition,
			@doc_definition = @doc_definition,
			@one_per_recipient = @one_per_recipient,
			@zip_attachments = @zip_attachments,
			@zip_definition = @zip_definition,
			@queue = @queue,
			@queue_definition =@queue_definition,
			@content_path = @content_path,
			@content_path_definition =@content_path_definition,
			@merge_from_query = @merge_from_query,
			@merge_from_query_definition =@merge_from_query_definition,
			@ansi_attachment=@ansi_attachment,
			@importance=@importance,
			@reply_to=@reply_to,
			@email_when_empty = @email_when_empty,
			@exception_mode = @exception_mode,
			@quote_all = @quote_all,
			@mail_server = @mail_server'
			)

IF @perms <> ''
	exec (@perms)



/****** CreateRollbackScript *******/

/*
This script records what permissions are granted or denied to xp_sendmail and outputs a script
that can be used to restore xp_sendmail on your machine.

IMPORTANT: please switch SQL Query Analyzer into the text mode by pressing Ctrl-T or 
going to the Main Menu, then Query and clicking on the Results in Text submenu.

The resulting script should be saved in case you need to restore original xp_sendmail on your server.
*/

print '--Rollback script'
print ''
print 'USE Master'
print 'GO'
print ''
print 'set nocount on'
print ''
print 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[xp_sendmail]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1) '
print 'begin'
print ''
print 'drop proc dbo.XP_SENDMAIL'
print 'EXEC sp_addextendedproc xp_sendmail, ''' + @dll + ''''

if @perms <> ''
	print @perms

print ''
print 'end'
print 'else'
print '     print ''Cannot find xp_sendmail stored procedure'''
print ''
print 'set nocount OFF'

set nocount OFF