文章/答案/技术大牛

发布

问允许dbmail访问帐户
EN

Database Administration用户

提问于 2015-10-09 14:41:53

回答 1查看 2.1K关注 0票数 1

我们有一个服务器，有许多(不断增加的)数据库，每个“数据库”都需要能够使用dbmail。每个(数据库的)邮件帐户是不同的，并通过自定义ui进行配置。我们创建了一个存储的proc，可以多次运行它来添加所需的msdb.dbo.sysmail_profile和msdb.dbo.sysmail_account。其内部来源于：在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

所以我们只运行proc，然后从每个数据库中查找实际的帐户设置。问题是我们缺少一些权限，需要编写它们的脚本。理想情况下，这应该是我们上面存储的proc的一部分(文章中的代码)。

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

到目前为止，我已经达到了对SQL脚本工作了解的极限。那么，是否有人能建议一种避免“使用”的方法，这是不能允许在程序内？

sql-server

sql-server-2014

permissions

database-mail

回答 1

Database Administration用户

回答已采纳

发布于 2015-10-09 14:58:18

动态字符串执行将允许您在proc中执行USE。注意，如果要在当前数据库中执行语句，在这些语句之后，您必须切换回它。

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)

票数 2

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/117565

复制

相似问题

问允许dbmail访问帐户
EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问允许dbmail访问帐户EN

回答 1

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问允许dbmail访问帐户
EN