Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!
Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:

>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!