September 22, 2013 at 1:31 am
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
September 22, 2013 at 1:43 pm
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
September 22, 2013 at 4:26 pm
Good Question Raul.
September 22, 2013 at 8:09 pm
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
September 23, 2013 at 1:35 am
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. 🙂
September 23, 2013 at 2:17 am
Didn't get the point but definitely learned something. Way to go!
September 23, 2013 at 2:25 am
This was removed by the editor as SPAM
September 23, 2013 at 3:34 am
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.
September 23, 2013 at 3:52 am
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
September 23, 2013 at 4:05 am
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.
September 23, 2013 at 4:57 am
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!
September 23, 2013 at 6:58 am
Thanks Raul.
September 23, 2013 at 7:11 am
Nice easy question for Monday Morning. Surprised that almost 50% have gotten it wrong so far. 😎
September 23, 2013 at 8:05 am
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