Disable [guest] database user in [msdb] database

  • Comments posted to this topic are about the item Disable

    database user in [msdb] database

  • Yes, in real life for production db`s it`s always a best security practice to disable guest user, that`s why it`s disabled in model database, but that dose not apply to other system databases like illustrated in the reference link in the answer.

    Good question Raul, thank you.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Very nice question, thanks.

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

  • Good Question Raul.

  • good question - had me thinking at a broader level rather than down to the individual databases - hence the wrong answer....

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Very good question. A topic very often left behind. though I was on the wrong side of answers, it definitely cleared basics about the 'guest' login. 🙂

  • Didn't get the point but definitely learned something. Way to go!

  • This was removed by the editor as SPAM

  • Interesting question. Yet I have another one:

    Why not disabling "guest" for the master database?

    I guess because SQL Server needs the access to the master database for extracting information from the system tables and views as well as for stored procs.

    Something like "sys.databases", "sys.messages" etc.

    I would have expected to see guest being member of the role "public" but I didn't see anything.

    Executing

    EXECUTE AS USER = 'guest';

    SELECT *

    FROM fn_my_permissions('sys.messages', 'Object')

    Returns me a SELECT permission.

    I want to know what exactly is the user "guest" allowed.

    I found a script here:

    http://www.sqlservercentral.com/scripts/Security/66129/

    Is there a shorter way to extract the (object level) permisions of a user?

    Does anyone have a link what the master database needs these permissions for?

    Best regards

    JP

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • Dscheypie (9/23/2013)


    Interesting question. Yet I have another one:

    Why not disabling "guest" for the master database?

    I guess because SQL Server needs the access to the master database for extracting information from the system tables and views as well as for stored procs.

    Something like "sys.databases", "sys.messages" etc.

    I would have expected to see guest being member of the role "public" but I didn't see anything.

    Executing

    EXECUTE AS USER = 'guest';

    SELECT *

    FROM fn_my_permissions('sys.messages', 'Object')

    Returns me a SELECT permission.

    I want to know what exactly is the user "guest" allowed.

    I found a script here:

    http://www.sqlservercentral.com/scripts/Security/66129/

    Is there a shorter way to extract the (object level) permisions of a user?

    Does anyone have a link what the master database needs these permissions for?

    Best regards

    JP

    In fact, you cannot disable the

    user either [master] nor [tempdb].

    USE [master]

    REVOKE CONNECT FROM

    Msg 15182, Level 16, State 1, Line 3

    Cannot disable access to the guest user in master or tempdb.

    What would be a good question for the MS guys is the reason of leaving the possibility of disabling

    in [msdb] although is not recommended...

    Cheers

  • Hm, true. And: Yes, good question for the MS guys.

    Apparently we cripple SQL Server by DENYing "guest" the access to msdb. So if one wants to avoid guests spying on our service architecture one should REVOKE access to the objects in question for "guest".

    Thank you for your answer, Raul!

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • Ah, dang it, you guys got me again after a weekend and before my first coffee. I was sure i was correct:

    SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. This recommendation does not apply to master, msdb, and tempb system databases. In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database.

    I read first sentence and DID NOT read rest of it. That will teach me! Of course I said that before too. Good question and thanks for waking me up!

  • Thanks Raul.

  • Nice easy question for Monday Morning. Surprised that almost 50% have gotten it wrong so far. 😎

  • So if you don't need the features that rely on guest access, you can disable it in msdb?

    So the correct answer should be "It depends on the security requirements"?

    The correct answer should always be "it depends."

    :discuss:

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

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