login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
Dan D.
You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.
|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:

> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>
>
|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.

> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.

> What happens if I remove guest from master?
You can't do that.

> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...[vbcol=seagreen]
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:

> Sysadmins will.
>
> Yes. But again, no need to add sysadmins.
>
> You can't do that.
>
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>
>
|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...[vbcol=seagreen]
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.

> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is[vbcol=seagreen]
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
one[vbcol=seagreen]
msdb and[vbcol=seagreen]
the[vbcol=seagreen]
of) the registry.[vbcol=seagreen]
one[vbcol=seagreen]
user[vbcol=seagreen]
msdb and[vbcol=seagreen]
master?[vbcol=seagreen]
the[vbcol=seagreen]
guest[vbcol=seagreen]
removed[vbcol=seagreen]
will[vbcol=seagreen]
that one[vbcol=seagreen]
for[vbcol=seagreen]
can I[vbcol=seagreen]
database.[vbcol=seagreen]