MITIGATE XP_SENDMAIL SECURITY BREACHES

Do your users have access to files on SQL server using SQL Mail? SQL
Mail runs under the same security profile under which SQL agent's 
account
runs. By default, SQL agent runs under the local system account. If 
users
can access the extended system stored procedure xp_sendmail within
databases on SQL server, then security breaches are possible.

Users may be able to gain access to files on that server by using the
attachment parameter of the extended system stored procedure 
xp_sendmail.
One method you can use to secure xp_sendmail is to encapsulate it into 
a
stored procedure and not make public the attachment parameter. 
Permissions
would be granted to the stored procedure but revoked from xp_sendmail.

The following is a basic working template you can use to secure
xp_sendmail.

use master
go
-- ==============================================
-- Create procedure basic template
-- ==============================================
-- creating the store procedure
IF EXISTS (SELECT name
         FROM     sysobjects
         WHERE    name = N'sp_sendmail'
         AND      type = 'P')
    DROP PROCEDURE sp_sendmail
GO

CREATE PROCEDURE sp_sendmail
     @in_recipients VARCHAR(8000) = '<default email address>'
    ,@in_message VARCHAR(8000)= 'test'
    ,@in_query VARCHAR(8000)= ''
    ,@in_copy_recipients VARCHAR(8000)= NULL
    ,@in_blind_copy_recipients VARCHAR(8000)= NULL
    ,@in_subject VARCHAR(80)= 'test'
    ,@in_type VARCHAR(80)= NULL
    ,@in_attach_results VARCHAR(80)= NULL
    ,@in_no_output VARCHAR(8)= NULL
    ,@in_no_header VARCHAR(8)= NULL
    ,@in_width INT = 10
    ,@in_separator VARCHAR(8)= NULL
    ,@in_echo_error VARCHAR(8000)= NULL
    ,@in_set_user VARCHAR(256) = NULL
    ,@in_dbuse VARCHAR(256) = NULL
AS

DECLARE @attachments VARCHAR(8000)

SET @in_recipients = '<default dba email address>;' + @in_recipients
exec master..xp_sendmail
     @recipients = @in_recipients
    ,@message = @in_message
    ,@query = @in_query
    ,@attachments =  ' '
    ,@copy_recipients = @in_copy_recipients
    ,@blind_copy_recipients = @in_blind_copy_recipients
    ,@subject = @in_subject
    ,@type = @in_type
    ,@attach_results = @in_attach_results
    ,@no_output = @in_no_output
    ,@no_header = @in_no_header
    ,@width = @in_width
    ,@separator = @in_separator
    ,@echo_error = @in_echo_error
    ,@set_user = @in_set_user
    ,@dbuse = @in_dbuse
GO

-- ==============================================
-- example to execute the store procedure
-- ==============================================
EXECUTE sp_sendmail
GO

-- =====================================================
-- example to grant permissions to the storre procedure
-- =====================================================
GRANT EXECUTE ON sp_sendmail TO public
GO
REVOKE EXECUTE ON xp_sendmail TO public
GO

----------------------------------------