Sql-Server

授予對帳戶的 dbmail 訪問權限

  • March 24, 2016

我們有一個帶有許多（不斷增加的）數據庫的伺服器，每個“數據庫”都需要能夠使用 dbmail。每個（數據庫的）郵件帳戶都是不同的，並通過自定義 ui 進行配置。我們創建了一個儲存過程，可以多次執行它來添加必要的 msdb.dbo.sysmail_profile 和 msdb.dbo.sysmail_account。其中的內容來自：在 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

所以我們只執行 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 腳本工作的知識的極限。那麼有人可以建議一種方法來避免在 procs 中不允許的“使用”嗎？

動態字元串執行將允許您在 proc 中執行 USE dbname。請注意，如果您在這些語句之後在目前數據庫中執行語句，則必須切換回它。

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)

引用自：https://dba.stackexchange.com/questions/117565

相關問答

Sql-Server

為 sysmail_update_account_sp 分配權限

  • November 10, 2016
檢視問答
Sql-Server

SET IDENTITY_INSERT 的權限是否低於 db_ddladmin？

  • April 3, 2022
檢視問答
Sql-Server

數據庫郵件使用哪個 TLS 版本？

  • February 8, 2022
檢視問答
Sql-Server

恢復 SA 密碼 - PSTools - 不工作

  • November 8, 2021
檢視問答
Sql-Server

數據庫郵件嘗試發送 2 次然後失敗

  • October 28, 2021
檢視問答
Sql-Server

允許使用者向其他人授予某些權限

  • February 12, 2021
檢視問答