We have a server with a number of (increasing) databases, each of the "databases" needs to be able to use dbmail. Each (database's) mail account is different and configured via a custom ui. We created a stored proc whic could be run multiple times to add then necessary msdb.dbo.sysmail_profile and msdb.dbo.sysmail_account. The innards of which were derived from: Using Database Mail in SQL Server 2008
CREATE PROC ai_SetupSMTPEmailProfileAndAccounts
AS
--adds profile if not exists or gets profile id
--adds account if not exists or gets account id
--adds account to profile if not exists
--makes account profile public for use
--http://www.idevelopment.info/data/SQLServer/DBA_tips/Database_Administration/DBA_20.shtml
DECLARE @DatabaseName VARCHAR(256)
DECLARE @EmailAccountDescription VARCHAR(256)
DECLARE @SMTPServerName VARCHAR(256)
DECLARE @SMTPPort INT
DECLARE @SMTPAccountName VARCHAR(256)
DECLARE @SMTPPassword VARCHAR(256)
DECLARE @SMTPFromAddress VARCHAR(256)
DECLARE @SSL INT = 0
DECLARE @DefaultCredentials INT = 0
DECLARE @AccountNr INT = 0
DECLARE @ProfileNr INT = 0
SET @DatabaseName = DB_NAME()
SET @EmailAccountDescription = @DatabaseName + ' email sending account'
IF (@DatabaseName = 'master') OR (@DatabaseName = 'msdb') OR (@DatabaseName = 'model') OR (@DatabaseName = 'tempdb')
BEGIN
PRINT 'Do not run on ' + @DatabaseName
RETURN
END
ELSE
BEGIN
IF NOT EXISTS( SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @DatabaseName)
BEGIN
PRINT 'profile not exists'
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @DatabaseName
, @description = 'auto db specific smtp profile'
, @profile_id = @ProfileNr OUTPUT --yup sql has the output prameters the wrong way around
END
ELSE
BEGIN
PRINT 'profile exists'
SET @ProfileNr = (SELECT TOP 1 profile_id FROM msdb.dbo.sysmail_profile WHERE name = @DatabaseName)
END
IF NOT EXISTS( SELECT * FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName)
BEGIN
PRINT 'account not exists'
SELECT TOP 1
@SMTPServerName = CASE WHEN COALESCE(WebAppSmtpServer, '') <> '' THEN
WebAppSmtpServer COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSmtpServer, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPPort = CASE WHEN COALESCE(WebAppSMTPPort, 587) <> 587 THEN
WebAppSMTPPort
ELSE
COALESCE(DbMSMTPPort, 587)
END
, @SMTPAccountName = CASE WHEN COALESCE(WebAppSMTPUser, '') <> '' THEN
WebAppSMTPUser COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPUser, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPPassword = CASE WHEN COALESCE(WebAppSMTPPwd, '') <> '' THEN
WebAppSMTPPwd COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPPwd, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPFromAddress = CASE WHEN COALESCE(WebAppSMTPFrom, '') <> '' THEN
WebAppSMTPFrom COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPFrom, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SSL = CASE WHEN COALESCE(WebAppSMTPSSL, 0) >= 2 THEN --sql and sql external
1
WHEN COALESCE(DbMSMTPSSL, 0) >= 2 THEN --sql and sql external
1
ELSE
0
END
, @DefaultCredentials = CASE WHEN COALESCE(WebAppSMTPSSL, 0) = 3 THEN --sql and sql external
1
ELSE
0
END
FROM
Rules2
ORDER BY
RulesKey2
PRINT 'rules 2 setting checked'
EXECUTE msdb.dbo.sysmail_add_account_sp
@email_address = @SMTPFromAddress
, @account_name = @DatabaseName
, @display_name = @DatabaseName
, @replyto_address = @SMTPFromAddress
, @description = @EmailAccountDescription
, @mailserver_name = @SMTPServerName
--[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported'
, @port = @SMTPPort
, @username = @SMTPAccountName
, @password = @SMTPPassword
, @use_default_credentials = 0 -- we will always have nor domain credientials @DefaultCredentials
--use account name and password
--not databse credentials
, @enable_ssl = @SSL -- bit 1 for use ssl
, @account_id = @AccountNr OUTPUT
PRINT '@SMTPFromAddress:' + ISNULL(@SMTPFromAddress, ' NULL')
PRINT '@DatabaseName:' + ISNULL(@DatabaseName, ' NULL')
PRINT '@EmailAccountDescription:' + ISNULL(@EmailAccountDescription, ' NULL')
PRINT '@SMTPServerName:' + ISNULL(@SMTPServerName, ' NULL')
PRINT '@SMTPPort:' + CAST(ISNULL(@SMTPPort, 0) AS VARCHAR(15))
PRINT '@SMTPAccountName:' + ISNULL(@SMTPAccountName, ' NULL')
PRINT '@SMTPPassword:' + ISNULL(@SMTPPassword, ' NULL')
PRINT '@DefaultCredentials:' + CAST(ISNULL(@DefaultCredentials, 0) AS VARCHAR(15))
PRINT '@SSL:' + CAST(ISNULL(@SSL, 0) AS VARCHAR(15))
PRINT 'tried to add account'
END
ELSE
BEGIN
PRINT 'account exists'
SET @AccountNr = ( SELECT TOP 1 account_id FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName ORDER BY account_id )
SELECT TOP 1
@SMTPServerName = CASE WHEN COALESCE(WebAppSmtpServer, '') <> '' THEN
WebAppSmtpServer COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSmtpServer, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPPort = CASE WHEN COALESCE(WebAppSMTPPort, 587) <> 587 THEN
WebAppSMTPPort
ELSE
COALESCE(DbMSMTPPort, 587)
END
, @SMTPAccountName = CASE WHEN COALESCE(WebAppSMTPUser, '') <> '' THEN
WebAppSMTPUser COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPUser, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPPassword = CASE WHEN COALESCE(WebAppSMTPPwd, '') <> '' THEN
WebAppSMTPPwd COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPPwd, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SMTPFromAddress = CASE WHEN COALESCE(WebAppSMTPFrom, '') <> '' THEN
WebAppSMTPFrom COLLATE SQL_Latin1_General_CP1_CI_AS
ELSE
COALESCE(DbMSMTPFrom, '') COLLATE SQL_Latin1_General_CP1_CI_AS
END
, @SSL = CASE WHEN COALESCE(WebAppSMTPSSL, 0) >= 2 THEN --sql and sql external
1
WHEN COALESCE(DbMSMTPSSL, 0) >= 2 THEN --sql and sql external
1
ELSE
0
END
, @DefaultCredentials = CASE WHEN COALESCE(WebAppSMTPSSL, 0) = 3 THEN --sql and sql external
1
ELSE
0
END
FROM
Rules2
ORDER BY
RulesKey2
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_id = @AccountNr
, @email_address = @SMTPFromAddress
, @account_name = @DatabaseName
, @display_name = @DatabaseName
, @replyto_address = @SMTPFromAddress
, @description = @EmailAccountDescription
, @mailserver_name = @SMTPServerName
--[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported'
, @port = @SMTPPort
, @username = @SMTPAccountName
, @password = @SMTPPassword
, @use_default_credentials = 0 -- we will always have nor domain credientials @DefaultCredentials
--use account name and password
--not databse credentials
, @enable_ssl = @SSL -- bit 1 for use ssl
END
PRINT CAST(@ProfileNr AS VARCHAR(15)) + ' @ProfileNr'
PRINT CAST(@AccountNr AS VARCHAR(15)) + ' @AccountNr'
-- the following are necessary !!!
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profileaccount WHERE profile_id = @ProfileNr AND account_id = @AccountNr)
BEGIN
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @DatabaseName,
@account_name = @DatabaseName,
@sequence_number = 1 ;
END
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_principalprofile WHERE profile_id = @ProfileNr)
BEGIN
-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @DatabaseName,
@principal_name = 'public',
@is_default = 1 ;
END
--SELECT name, [Description], profile_id, 0 account_id, 'profile' TableName FROM msdb.dbo.sysmail_profile
--UNION
--SELECT name, [Description], 0 profile_id, account_id, 'email account' TableName FROM msdb.dbo.sysmail_account
END
--cycle db mail mail status
EXEC Master.dbo.sp_configure 'Database Mail XPs', 0
RECONFIGURE
EXEC Master.dbo.sp_configure 'Database Mail XPs', 1
RECONFIGURE
RETURN
So we just run the proc and then the actual account settings are looked up from each database. The problem is we are missing some permissions and need to script them. Ideally this should form part of our stored proc above (code from article)
USE [master]
Go
-- Create a login account to use Windows Authentication
CREATE LOGIN [IDEVELOPMENT\AppUser] FROM WINDOWS WITH
DEFAULT_DATABASE = [AppDB];
Go
--
-- Create a user in the [msdb] database
USE [msdb]
Go
CREATE USER [AppUser] FOR LOGIN [IDEVELOPMENT\AppUser];
Go
GRANT CONNECT TO [AppUser];
Go
--
USE [msdb]
Go
-- Add user to the Database Mail role
EXEC sp_addrolemember
@rolename = 'DatabaseMailUserRole'
, @membername = 'AppUser';
Go
-- Grants permission for a database user or role
-- to use a private Database Mail profile
EXEC msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'iDevelopment.info Profile'
, @principal_name = 'AppUser'
, @is_default = 1;
Go
At this point I have reached the limit of my knowledge of SQL scripting work arounds. So can someone suggest a way to avoid "use" which cannot be allowed inside procs?
Best Answer
Dynamic string execution will allow you to execute USE dbname in a proc. note if you have statements to be executed in the current database after these you will have to switch back to it.