Disable [guest] database user in [msdb] database

Question

Disable [guest] database user in [msdb] database

SQLDoubleG

Hall of Fame

Points: 3100
More actions
September 21, 2013 at 12:25 pm

#298955

Comments posted to this topic are about the item Disable
database user in [msdb] database
September 23, 2013 at 2:25 am

This was removed by the editor as SPAM

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

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

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 More actions · Answer 3

PChiragS

SSCarpal Tunnel

Points: 4965

September 22, 2013 at 4:26 pm

#1652223

Good Question Raul.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 4

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: 17437 More actions · Answer 5

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 More actions · Answer 6

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

Dscheypie SSCommitted Points: 1545 More actions · Answer 7

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: 3100 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 3745 More actions · Answer 11

Tee Time

Hall of Fame

Points: 3745

September 23, 2013 at 6:58 am

#1652323

Thanks Raul.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 12

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

sknox SSChampion Points: 12388 More actions · Answer 13

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: