I have a Microsoft SQL Server 2014. The mail system is configured via DBMail and there is one stored proc in main web app (having its own database) which executes msdb's sysmail_update_account_sp by selecting the appropriate profile using msdb.dbo.sysmail_account.
The problem I am facing is the web application has a SQL login and I want to switch context and execute sysmail_update_account_sp.
EXEC AS USER = @UserName SET @AccountNr = ( SELECT TOP 1 account_id FROM msdb.dbo.sysmail_account WHERE name = @DatabaseName ORDER BY account_id ) IF @AccountNr > 0 EXECUTE msdb.dbo.sysmail_update_account_sp @account_id = @AccountNr , @email_address = @ParamSMTPFrom , @account_name = @DatabaseName , @display_name = @DatabaseName , @replyto_address = @ParamSMTPFrom , @description = @EmailAccountDescription , @mailserver_name = @ParamWebAppSmtpServer --[ , [ @mailserver_type = ] 'server_type' ] 'optional only SMTP supported' , @port = @ParamWebAppSMTPPort , @username = @ParamWebAppSMTPUser , @password = @ParamWebAppSMTPPwd , @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 REVERT
Error occurring is:
Cannot execute as the database principal because the principal "SERVERNAME\dbMailUser" does not exist, this type of principal cannot be impersonated, or you do not have permission
Things I checked:
- SERVERNAME\dbMailUser has DatabaseMailUserRole permission in msdb currently.
- If I add the executing stored proc user (from web app) as sysadmin,I don't have to switch or anything. But I want to limit the permission to it's minimum.
How can I execute as a different user (SQL or Windows Login) by switching context properly? The current SQL login is a database owner.
The requirement is the login should be able to configure DBMail from a stored procedure and able to send mails (the DatabaseMailUserRole), without it being in the sysadmin role.
Am I in the right direction, regarding my understanding of how DBMail configuration works?