Checking sp_send_email permission before executing

Question

In my stored procedure, I send emails with sp_send_email. My stored procedure will be run in different environments; some will have emailing enabled, some won't.

If I run sp_send_email without it being enabled, I (quite rightly) get this error message

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server.

I want to check whether emailing is enabled first, so I can avoid the error by doing the following:

IF @is_enabled
BEGIN
    EXEC sp_send_email ...
END

How do I correctly set @is_enabled?

Answer 1

BOL says:

To send Database mail, users must be a user in the msdb database and a member of the DatabaseMailUserRole database role in the msdb database. To add msdb users or groups to this role use SQL Server Management Studio or execute the following statement for the user or role that needs to send Database Mail.

So, you can give to user the appropriate role:

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = '<user or role name>'; GO

Answer 2

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs';
GO

The sp_send_mail falls under the 'Database Mail XPs' umbrella. You application may not have the rights to run sp_configure and RECONFIGURE, so in the end you may be better if you simply try to invoke the sp_send_mail and handle the error.

Answer 3

You can query sys.configurations

IF EXISTS (SELECT *
       FROM sys.configurations
       WHERE name = 'Database Mail XPs' AND value_in_use = 1)
    PRINT 'enabled'
ELSE
    PRINT 'no luck'

The downside is that it may not be visible to non-sysadmin users because of "MetaData Visibility"