Assigning permissions for sysmail_update_account_sp

Question

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?

Answer 1

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.

Answer 2

sysmail_update_account_sp requires membership in the sysadmin fixed server role. This means that DatabaseMailUserRole or any other msdb fixed database role is not enough.

Using EXECUTE AS LOGIN to impersonate a sysadmin login from your non-privileged login completely defeats the purpose of using a non-privileged user, as it can escalate permissions at any time (requires IMPERSONATE permissions).

I would recommend to wrap the code you have to execute in a stored procedure and the sign it with a certificate. The technique is discussed a lenght on Erland Sommarskog's blog. That page goes into deep details of all the possible solutions, with downsides and upsides to each of them.