Disable [guest] database user in [msdb] database

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Comments posted to this topic are about the item Disable

    database user in [msdb] database

  • Hany Helmy

    SSChampion

    Points: 13488

    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.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    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

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Good Question Raul.

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    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

  • sqlnaive

    SSCoach

    Points: 17435

    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. 🙂

  • ksatpute123

    Hall of Fame

    Points: 3325

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

  • This was removed by the editor as SPAM

  • Dscheypie

    SSCommitted

    Points: 1545

    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.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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

  • Dscheypie

    SSCommitted

    Points: 1545

    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.

  • Michael_Garrison

    Hall of Fame

    Points: 3110

    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!

  • Tee Time

    Hall of Fame

    Points: 3693

    Thanks Raul.

  • PHYData DBA

    SSCertifiable

    Points: 7541

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

  • sknox

    SSChampion

    Points: 12284

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

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