public role in MS sql server

Hi,

I've been learned that the public role can be used when we want only a specific group of users that can access the server to have access to a specific database.
Let's say that I have 2 departments: deptA and deptB
I also have 2 DBs: DB_A and DB_B
Now I create global groups GA and GB and these are mapped to DB users, userA and userB respectively.

The requiremetn is: people in deptA can access to both DB_A and DB_B, but people in deptB can access to only DB_B and I need to use only 'public role' to solve this problem.
I guess that since userA and userB are in the database, they both have the permissions assigned to the Public role.  What kind of permissions should I grant to the Public role to resolve this issue?



How can I accomplish this?
IzzyTwinklyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TempDBACommented:
you can add map the domain to both the user.
0
IzzyTwinklyAuthor Commented:
Hi TempDBA,
I don't think that's the answer that I want.(I think I might explain the issue poorly, but please understand me since I am still a beginner).
public role is in database level, so DB_A and DB_B will have separate public roles for each, correct?

Here are steps that I think I should take.
1. In public role in DB_A, I add 'userA' as a role member and in public role in DB_B, I add 'userB' as a role member.
2. In public role in DB_A. I add secuable(database), DB_A and DB_B.
3. In public role in DB_B, I add secuable(database), DB_B only.

In this way userA can access to DB_A and DB_B using public role in DB_A.
Also, userB can access to DB_B using public role in DB_B.  

I might be wrong.  Please let me know if this is the correct way to use the public role for the scenario I stated in my question.
0
LowfatspreadCommented:
is this a home/course work related question?

normally you would only use the Public group for a development database , any usage against a production database would be
considered in-secure.

0
IzzyTwinklyAuthor Commented:
Hi Lowfatspread,

No I got this scenario from the book i am working on.  I just wanted to make sure I got the right idea about public role and the scenario provided by the book.
0
Scott PletcherSenior DBACommented:
By default every user will get the public role, you don't have to grant it to them.

To keep DbA more restricted, simply revoke priviledges from public in that db.


>> I've been learned that the public role can be used when we want only a specific group of users that can access the server to have access to a specific database. <<

Not sure about that.  As I said, everyone is automatically a member of the public role.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.