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';