SQL DBA - How to Grant Execute Permission To User on msdb.dbo.sp_send_dbmail in SQL Server

The below script can be used step by step to create login, user and finally provide execute permission to user on sp_send_dbmail stored procedure in SQL server so the user can send emails.


--Create Login for Windows account.You can create SQL Server Login if you want
USE master
go
CREATE LOGIN [DomainName\UserName] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

--Create User in MSDB Databse
USE msdb
go
CREATE USER [DomainName\UserName] FOR LOGIN [DomainName\UserName]
GO

--Get the Profile Name that we need to use 
select * from msdb..sysmail_profile



--Assign the DatabaseMailUserRole to user
USE [msdb]
GO
EXEC sp_addrolemember N'DatabaseMailUserRole', N'DomainName\UserName'
GO

--Grand Execute permission on sp_send_dbmail procedure
Grant EXEC on sp_send_dbmail to [DomainName\UserName]



--Grants permission for a database user or role to use a Database Mail profile.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MailProfileName',
    @principal_name = 'DomainName\UserName',
    @is_default = 1 ;
    
  
--Test if all working   
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name ='MailProfileName',
    @recipients = 'aamir_warraich@hotmail.com',
    @body = 'Test Body',
    @subject = 'Test Sub';

2 comments:

  1. Enjoyed reading the article above, really explains everything in detail, the article is very interesting and effective. Thank you and good luck for the upcoming articles sql dba training training

    ReplyDelete