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:
...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
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,@firstname.lastname@example.org',@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.',@email@example.com',@p6=2,@RETURN_VALUE=@p10 output select @p10
User does not have permission to perform this action.
So how stupid are we being and more importantly where?