SQL Server – How to Assign Permissions for sysmail_update_account_sp

database-mailpermissionssignaturesql serversql server 2014

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:

  1. SERVERNAME\dbMailUser has DatabaseMailUserRole permission in msdb currently.
  2. 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?

Best Answer

This request is very similar to, if not the same as, another question on here (DBA.StackExchange) regarding the need for very granular elevated permissions. I provided working code in my answer, and that could be used here as well:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

The overall concept is the same: give the Stored Procedure itself the required permission by being associated (via the Certificate) with a Login that has the proper permission. The only difference would be that instead of granting VIEW SERVER STATE TO [MrDoStuff], you would probably just add [MrDoStuff] to the sysadmin fixed server-level role.

One difference, though, between this question and the "What minimum permissions..." question is that the other question simply needed access to a DMV and not to any code objects outside of the code being signed. It just so happens that signatures do not, by default, transfer to code called by the signed code. But there is a way of allowing this to happen: counter-sign the external code being called. Counter-signing allows code executed by signed-code to transfer the signature without actually being signed itself (and so the external code cannot be called independently). Counter signing is handled by the same ADD SIGNATURE function that was used to sign the initial Stored Procedure:

  1. Restore the Certificate into the [msdb] database
  2. Counter-sign the dbo.sysmail_update_account_sp Stored Procedure

Continuing the example code from that other answer, the additional steps should be:

USE [msdb];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD COUNTER SIGNATURE TO dbo.sysmail_update_account_sp
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

It is possible that if sysmail_update_account_sp executes any stored procedures, then they might also need to be counter-signed.

Related Question