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?

up vote 1 down vote accepted

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.

  • Hi @srutzky, I followed the steps as you mentioned and added the user to sysadmin role, So when I execute the stored proc now I am getting :- User does not have permission to perform this action.(at the place where I call msdb update function for DbMail configuration). I believe I don't have to switch context because that was the whole point of going via certificate route ( the created login is mapped to msdb and database under question) having sysadmin role. Why should that error be appearing in that case? – Rohith Nair Nov 10 '15 at 13:48
  • @RohithNair Correct, you should not need the EXECUTE AS at all using this approach. I believe the issue is the need for a counter signature. I have updated my answer by adding info starting with "One difference...". Please let me know how that works. If not, then it might just be that one remaining step is to create a User in msdb from the [MrDoStuff] Login. But let's try just this next piece by itself first. – Solomon Rutzky Nov 10 '15 at 17:18
  • I added the counter signature and it started working. I didn't add the user as sysadmin but gave permissions to alter settings, control server and alter any credential , because when I gave sysadmin I couldn't see user token of certificate being propagated to inner stored procs in msdb – Rohith Nair Nov 12 '15 at 15:46
  • @RohithNair That is perfect. There is no reason to give more permissions than is necessary to complete the desired tasks. So if you can get away without putting the Login into the sysadmin role, then great. Glad it is working for you :). – Solomon Rutzky Nov 12 '15 at 16:07
  • just to add if the mail you are sending l is having attachments then the above said method will fail, it requires access permissions using sysadmin role – Rohith Nair Nov 11 '16 at 11:53

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.