MSDB Tables/Views

  • John Hanrahan

    Hall of Fame

    Points: 3825

    Comments posted to this topic are about the item MSDB Tables/Views

  • baabhu

    SSCertifiable

    Points: 6202

    I did not knew this. In the end, I guessed. +1.

    Nice to knew this table. As we do so much on alerting.

    Thank you.

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice clear concise question. Thanks

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • M&M

    SSC-Insane

    Points: 21699

    Good one. Steve.

    M&M

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Gobikannan

    SSCrazy

    Points: 2735

    Nice question.

    -----------------
    Gobikannan

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    mine mine mine the one is mine. 🙂

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    And here comes the first complaint of the day (sorry) 😛

    The answer is wrong. The correct answer should be dbo.sysmail_mailitems (which is a table), but it is not listed as an option.

    The view will only return all mail items if the user executing it is member of the sysadmin role. Otherwise it will only return mail items sent by the current user.

    And here is the view definition that proves my disagreement (check the WHERE clause):

    CREATE VIEW sysmail_allitems

    AS

    SELECT mailitem_id,

    profile_id,

    recipients,

    copy_recipients,

    blind_copy_recipients,

    subject,

    body,

    body_format,

    importance,

    sensitivity,

    file_attachments,

    attachment_encoding,

    query,

    execute_query_database,

    attach_query_result_as_file,

    query_result_header,

    query_result_width,

    query_result_separator,

    exclude_query_output,

    append_query_error,

    send_request_date,

    send_request_user,

    sent_account_id,

    CASE sent_status

    WHEN 0 THEN 'unsent'

    WHEN 1 THEN 'sent'

    WHEN 3 THEN 'retrying'

    ELSE 'failed'

    END as sent_status,

    sent_date,

    last_mod_date,

    last_mod_user

    FROM msdb.dbo.sysmail_mailitems

    WHERE (send_request_user = SUSER_SNAME()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)

  • R.P.Rozema

    SSChampion

    Points: 12300

    And again I learned something. I've never had to use dbmail so far. So I googled and quickly found the right answer 😉

    Thanks



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    thats the whole point.

    not sure why this is an issue? as a end user can't go and query the MSDB on the table you have mentioned.

    And such task are mainly handled by SYSADMIN roles people.

    :unsure:

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Nils Gustav Stråbø (3/6/2012)


    And here comes the first complaint of the day (sorry) 😛

    So this view properly implements row-level security, allowing a "normal" user to see only his onw stuff and an admin to see everything - and you complain about it? :hehe::-P


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Hugo Kornelis (3/6/2012)


    Nils Gustav Stråbø (3/6/2012)


    And here comes the first complaint of the day (sorry) 😛

    So this view properly implements row-level security, allowing a "normal" user to see only his onw stuff and an admin to see everything - and you complain about it? :hehe::-P

    I don't complain about the functionality of that specific view, I'm just being an annoying pedantic 😀 Every QOTD has one.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Nils Gustav Stråbø (3/6/2012)


    Hugo Kornelis (3/6/2012)


    Nils Gustav Stråbø (3/6/2012)


    And here comes the first complaint of the day (sorry) 😛

    So this view properly implements row-level security, allowing a "normal" user to see only his onw stuff and an admin to see everything - and you complain about it? :hehe::-P

    I don't complain about the functionality of that specific view, I'm just being an annoying pedantic 😀 Every QOTD has one.

    sorry to hear that my friend, "pedantic" is the one which we need to give up. 🙂 (it never brings happiness)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Nils Gustav Stråbø

    SSChampion

    Points: 11259

    Raghavendra, please don't take my "complaints" seriously.

    Every QOTD has at least one guy/girl complaining about microscopic details in the phrasing of the question or answer, either forgetting to mention server versions, grammar, dialect and so on.

    My comment was basically meant as a joke (even though there is a hint of truth in all jokes), hence the smiley in my first post. If I hadn't mentioned it, then be sure that someone else would have 😉

Viewing 15 posts - 1 through 15 (of 31 total)

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