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';
Great article!!
ReplyDeleteThanks,
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