Send Mail

  • itmasterw 60042

    SSCrazy

    Points: 2605

    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 4 days, 12 hours ago by  Dave Convery.
  • Thom A

    SSC Guru

    Points: 98207

    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.

  • itmasterw 60042

    SSCrazy

    Points: 2605

    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: 2605

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

    Thanks

  • itmasterw 60042

    SSCrazy

    Points: 2605

    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: 98207

    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.

  • itmasterw 60042

    SSCrazy

    Points: 2605

    thanks this is really helpful.

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

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