Geek asks and answers

How to grant sendmail permission to sql server user?

I have a database user who owns the database. The requirement for the application is to send mail using the SQL Server mail database.

Is there a way in which I can add permission to send mail only for this user?

I have a user named testuser with server roles public and db_owner for 1 database. Please tell me how I do not need to give the sysadmin server for this user.

+5
source share
4 answers

Try using the following options.

USE msdb;
--add our user
CREATE USER  ClarkKent FOR LOGIN  ClarkKent; 
--give this user rights to use dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'ClarkKent'

, , ClarkKent , ;

USE msdb;
--add our user via a group we know he is in 
CREATE USER 'mydomain\BusinessGroup' FOR LOGIN   'mydomain\BusinessGroup'; 
--give this GROUP rights to use dbmail
exec sp_addrolemember 'DatabaseMailUserRole', 'mydomain\BusinessGroup'
+7

enter image description here

you need to add "DatabasemailUserRole" for the MSDB database for the user

0
source

You need to assign a login (or role) to the mail profile after adding it to the mail database role. For the script below, a default value can be set instead of zero.

use msdb
exec sp_addrolemember 'DatabaseMailUserRole', 'sqlUser'

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
@principal_name = 'sqlUser',  
@profile_name = 'sqlMailProfileName',
@is_default = 0
0
source

More articles:


All Articles