1

We want to grant a user the ability to update their own mail connection profile used by db mail in msdb. For example, when the IT company maintaining the mail server wants to change the password.

We don't want to give them any other rights. The stored proc we have contains the line:

EXECUTE msdb.dbo.sysmail_update_account_sp 

...which fails because sysmail_update_account_sp requires sysadmin rights. We are trying to give the permission via signing the procedure. The following code was derived from

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

USE [master];
go

-- DROP CERTIFICATE [MailSetupCert];

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'MailSetupCert')
    BEGIN
        PRINT 'trying to create certificate MailSetupCert'
        CREATE CERTIFICATE [MailSetupCert]
            ENCRYPTION BY PASSWORD = N'OurSp3cialPassW0rd!!'
            WITH SUBJECT = N'Certificate for Managing Special Permissions';
    END

CREATE LOGIN [dbMailAdmin] FROM CERTIFICATE [MailSetupCert];
GRANT CONTROL SERVER TO [dbMailAdmin];#
-- also tried EXEC sp_addsrvrolemember @loginame = N'dbMailAdmin', @rolename = N'sysadmin'

BACKUP CERTIFICATE [MailSetupCert]
    TO FILE = 'C:\TEMP\EmailSetup.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\EmailSetup.PVK',
        DECRYPTION BY PASSWORD = 'OurSp3cialPassW0rd!!',
        ENCRYPTION BY PASSWORD = 'AnotherSp3cialPassW0rd!!'
    );
                                                            -
USE [UserDatabase];
go

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'MailSetupCert')
    BEGIN
        PRINT 'trying to create certificate MailSetupCert from file'
        CREATE CERTIFICATE [MailSetupCert]
            FROM FILE = 'C:\temp\EmailSetup.CER'
            WITH PRIVATE KEY (
                FILE = 'C:\temp\EmailSetup.PVK',
                DECRYPTION BY PASSWORD = 'AnotherSp3cialPassW0rd!!',
                ENCRYPTION BY PASSWORD = 'OurSp3cialPassW0rd!!'
            );
    END

We follow this by:

ADD SIGNATURE TO dbo.ai_UpdateWebSetupEmail
    BY CERTIFICATE [MailSetupCert] WITH PASSWORD = 'OurSp3cialPassW0rd!';

GRANT EXEC ON ai_UpdateWebSetupEmail TO DatabaseUser

When we execute as DatabaseUser:

declare @p10 int
set @p10=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[ai_UpdateWebSetupEmail] @ParamWebAppSmtpServer = @p0, @ParamWebAppSMTPPort = @p1, @ParamWebAppSMTPUser = @p2, @ParamWebAppSMTPPwd = @p3, @ParamWebAppEmailMessageDisclaimer = @p4, @ParamSMTPFrom = @p5, @ParamSMTPSSL = @p6',N'@p0 varchar(8000),@p1 int,@p2 varchar(8000),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),@p6 int,@RETURN_VALUE int output',@p0='smtp.gmail.com',@p1=587,@p2='testmail@nosuchdomain.com',@p3='emailpassword',@p4='The Email Disclaimer: IMPORTANT: This email is intended for the use of the individual addressee(s) named above and may contain information that is confidential, privileged or unsuitable for overly sensitive persons with low self-esteem, no sense of humour or irrational religious beliefs. If you are not the intended recipient, any dissemination, distribution or copying of this email is not authorised (either explicitly or implicitly) and constitutes an irritating social faux pas. Unless the word absquatulation has been used in its correct context somewhere other than in this warning, it does not have any legal or  grammatical use and may be ignored. No animals were harmed in the transmission of this email, although the kelpie next door is living on borrowed time, let me tell you. Those of you with an overwhelming fear of the unknown will be gratified to learn that there is no hidden message revealed by reading this warning backwards, so just ignore that Alert Notice from Microsoft.',@p5='testmail@nosuchdomain.com',@p6=2,@RETURN_VALUE=@p10 output
select @p10

We get:

User does not have permission to perform this action.

So how stupid are we being and more importantly where?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.