Send Mail

Question

Send Mail

itmasterw 60042

SSCrazy

Points: 2858
More actions
July 12, 2019 at 2:13 pm

#3662396
Hi,
I am new to this area, but we run the following and am getting that the user or role is not in the Database.
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
,@membername = '<user or role name>';
How can I query the database to see who is in there, and what they have?
Thank you
- This topic was modified 5 years, 4 months ago by Dave Convery.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

You should really avoid usage of sp_addrolemember; it has been deprecated for some time. sp_addrolemember (Transact-SQL):

Important
This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER ROLE instead.

When assigning a role, you need to ensure you are in the correct database. Does the below work?

USE msdb;

ALTER ROLE DatabaseMailUserRole ADD MEMBER {Your Database User};

If you want to find out who has a specific role, you can use the below:

USE msdb; --Or other appropriate DB

SELECT r.[name] AS RoleName,
       u.[name] AS [UserName]
FROM sys.database_principals r
     JOIN sys.database_role_members drm ON r.principal_id = drm.role_principal_id
     JOIN sys.database_principals u ON drm.member_principal_id = u.principal_id
WHERE r.type = N'R'
  AND u.type = N'U';

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

Okay thanks for that.

But my question was is there a way to query the database to see who is in it and what roles they have?

Thank you

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 3

Sorry I did not understand, but you have it here.

Thanks

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

When I run the query above I do not see anything about the mail in it's results.

But we di us send mail all the time. this is why I thought that there was a separate database for mail?

Thom A SSC Guru Points: 99130 More actions · Answer 5

itmasterw 60042 wrote:

When I run the query above I do not see anything about the mail in it's results. But we di us send mail all the time. this is why I thought that there was a separate database for mail?

You need the DatabaseMailUserRole database role in msdb to send emails in SQL Server, but to send an email you need to use sp_send_dbmail. If you don't have the DatabaseMailUserRole database role, then you would get an error when using sp_send_dbmail. If you therefore aren't getting an error, this suggests your emails are failing to send. I suggest having a look at your logs in the database. This'll help you start:

USE msdb;
GO

SELECT *
FROM dbo.sysmail_faileditems fi
     JOIN dbo.sysmail_log l ON fi.mailitem_id = l.mailitem_id
ORDER BY fi.mailitem_id DESC,
         l.log_id DESC;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 6

itmasterw 60042

SSCrazy

Points: 2858

July 12, 2019 at 4:26 pm

#3662434

thanks this is really helpful.