Post reply

MSDB Tables/Views

  • March 5, 2012 at 8:44 pm

    #269426

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

  • March 5, 2012 at 8:46 pm

    #1455089

    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.

  • March 5, 2012 at 9:24 pm

    #1455094

    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]

  • March 5, 2012 at 10:32 pm

    #1455103

    Good one. Steve.

    M&M

  • March 5, 2012 at 11:17 pm

    #1455114

    Nice question, thanks.

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

  • March 6, 2012 at 12:41 am

    This was removed by the editor as SPAM

  • March 6, 2012 at 12:59 am

    #1455135

    Nice question.

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

  • March 6, 2012 at 2:31 am

    #1455150

    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.

  • March 6, 2012 at 2:59 am

    #1455158

    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)

  • March 6, 2012 at 3:25 am

    #1455166

    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?

  • March 6, 2012 at 3:28 am

    #1455169

    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.

  • March 6, 2012 at 3:29 am

    #1455171

    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/

  • March 6, 2012 at 3:46 am

    #1455173

    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.

  • March 6, 2012 at 3:53 am

    #1455175

    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.

  • March 6, 2012 at 4:05 am

    #1455179

    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 30 total)

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