db_datareader role**

Hi
I defined a new login"login1" in SQL Server 2000 and
tried to make an access(public+db_datareader)
to one of databases called "db1" successfully.
then in query analyzer I logined by "login1" and
saw the name of more than one databases in left window
,there were ("master","tempdb","distribution",
"db1","northwind","pubs","msdb"),and when I tried to open them ,for
example I selected "tempdb" and
then clicked the right key of mouse on a view
called "dbo.sysconstraints" and it opened successfully,why? I want my user
just read the information of one database called "db1"!!!!
second question: why didn't appear other databases
in left window of query analyzer?
3th question ,what's the best selection to access
"login1" just reading the information(just select statement) stored in
"db1"?(for example:
is it correct to be member of public role and then
check the check boxes of select column in permission
section of all tables in "db1" one by one!?)
any help would be greatly appreciated.
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/A login can access a database only if the login is a user in the database or
the guest user is enabled. Since any login can access a database containing
the guest user, the other databases listed must have the guest user enabled
and this is why 'Login1' can access those databases even without a database
userid. Note that the guest user is required in the master and tempdb
system databases but can be removed from other databases if you don't want
all logins to have access to those databases.

> I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why
The public role has SELECT permissions on system tables and views. This is
needed in order to retrieve meta-data needed by database access APIs.

> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
Adding the user to only the db_datareader role will provide SELECT
permissions on tables and views. If you need more granular permissions, you
can create your own database role and grant the desired permissions to the
role. This allows you to control user database permissions via role
membership without granting direct permissions to individual users.
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4uk7scihqligo@.msnews.microsoft.com...
> Hi
> I defined a new login"login1" in SQL Server 2000 and
> tried to make an access(public+db_datareader)
> to one of databases called "db1" successfully.
> then in query analyzer I logined by "login1" and
> saw the name of more than one databases in left window
> ,there were ("master","tempdb","distribution",
> "db1","northwind","pubs","msdb"),and when I tried to open them ,for
> example I selected "tempdb" and
> then clicked the right key of mouse on a view
> called "dbo.sysconstraints" and it opened successfully,why? I want my user
> just read the information of one database called "db1"!!!!
> second question: why didn't appear other databases
> in left window of query analyzer?
> 3th question ,what's the best selection to access
> "login1" just reading the information(just select statement) stored in
> "db1"?(for example:
> is it correct to be member of public role and then
> check the check boxes of select column in permission
> section of all tables in "db1" one by one!?)
> any help would be greatly appreciated.
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||How can I recognize a guest user in a database?
On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> A login can access a database only if the login is a user in the
> database or
> the guest user is enabled. Since any login can access a database
> containing
> the guest user, the other databases listed must have the guest user
> enabled
> and this is why 'Login1' can access those databases even without a
> database
> userid. Note that the guest user is required in the master and tempdb
> system databases but can be removed from other databases if you don't
> want
> all logins to have access to those databases.
>
> The public role has SELECT permissions on system tables and views. This
> is
> needed in order to retrieve meta-data needed by database access APIs.
>
> Adding the user to only the db_datareader role will provide SELECT
> permissions on tables and views. If you need more granular permissions,
> you
> can create your own database role and grant the desired permissions to
> the
> role. This allows you to control user database permissions via role
> membership without granting direct permissions to individual users.
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> How can I recognize a guest user in a database?
To see if the guest user is enabled in a particular database:
USE MyDatabase
sp_helpuser 'guest'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"RM" <m_r1824@.yahoo.co.uk> wrote in message
news:opr4vwbsg8hqligo@.msnews.microsoft.com...
> How can I recognize a guest user in a database?
> On Sun, 14 Mar 2004 10:00:15 -0600, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
>
>
> --
> Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/