1

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?

2

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.

DECLARE @sql varchar(max)

SET @sql = '
USE [master]

-- Create a login account to use Windows Authentication
CREATE LOGIN [IDEVELOPMENT\AppUser] FROM WINDOWS WITH
    DEFAULT_DATABASE = [AppDB];

'
                                                            --

-- Create a user in the [msdb] database
EXEC(@sql)

SET @sql = '
USE [msdb]
CREATE USER [AppUser] FOR LOGIN [IDEVELOPMENT\AppUser];
GRANT CONNECT TO [AppUser];

-- Add user to the Database Mail role
EXEC sp_addrolemember
    @rolename = ''DatabaseMailUserRole''
  , @membername = ''AppUser'';

-- 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
'

EXEC (@sql)  
1
  • Many thanks, that solves the question I asked.
    – user143317
    Oct 13 '15 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.