Karim (karim3411@!!ya hoo!!.com) writes:
I installed SQL Server, created a database for a sql server user and
noticed that the user has access to the master database even though the
checkbox for master database for the user login is not checked.
They can list sysusers and find out all the names. They can list all the
databases as well by using sp_helpdb.
Is this normal behavior?
Yes. This is because the guest user is present in master. This means that
even if your login does not map to a specific user in master, your login
maps to guest. And Books Onlines says that guest must be present in master.
If I check db_denydataread er and db_denydatawrit er in the master database
for that user, will that break anything?
Well, to add the login to this role, you would first have to add the user.
But you could add guest to these roles. And, yes, that will break things.
I did a quick test. When I tried to login as a plain user, I got a
permission error on spt_values.
Possibly you could deny access on some tables, but I suspect that you
would be wondering off in the land of unsupported.
The good news is that in the next version of SQL Server, the metadata is
not equally well exposed, and the basic principle is that you should only
see the objects that you have permission to. That is, you may still be
able to read sys.databases, but you would only see the databases you
have permission to.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarsk og.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
