Managing SQL database rights using AD

I’m new to SQL so maybe there is an obvious reference that I have overlook
ed
that someone can point me to.
The problem I am trying to solve is managing SQL database rights using AD.
To simplify my problem imagine I have a single SQL server (SQL 2000). I also
have an app that can be accessed by two different organizations. Within each
organization I have two sets of users: Casual-users that can see a limited
number of tables and then Power-users that can see all tables for their
organization.
The structure can be conceptualized as something like this in AD:
- MyDomain
- Org1
-- Org1 admins
-- Org1 Power-Users
-- Org1 Casual-Users
- Org2
-- Org2 admins
-- Org2 Power-Users
-- Org2 Casual-Users
I can use AD to set up the structure I need. However, I would like to use
this AD structure to manage the user’s database access rights. Ideally I
could add a new user somewhere in my AD defined Domain and they would
automatically have the correct database rights.
Questions:
- Is the scenario that I am describing possible to implement?
- If so is there a source of information someone can point me at?
Thanks,
-AnthonyAnthony
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
Also , you may want to look into a database role to manage users rights
"Anthony" <Anthony@.discussions.microsoft.com> wrote in message
news:BEDEB701-EF66-4827-8D3A-F79F6C7EA284@.microsoft.com...
> Im new to SQL so maybe there is an obvious reference that I have
> overlooked
> that someone can point me to.
> The problem I am trying to solve is managing SQL database rights using AD.
> To simplify my problem imagine I have a single SQL server (SQL 2000). I
> also
> have an app that can be accessed by two different organizations. Within
> each
> organization I have two sets of users: Casual-users that can see a limited
> number of tables and then Power-users that can see all tables for their
> organization.
> The structure can be conceptualized as something like this in AD:
> - MyDomain
> - Org1
> -- Org1 admins
> -- Org1 Power-Users
> -- Org1 Casual-Users
> - Org2
> -- Org2 admins
> -- Org2 Power-Users
> -- Org2 Casual-Users
>
> I can use AD to set up the structure I need. However, I would like to use
> this AD structure to manage the users database access rights. Ideally I
> could add a new user somewhere in my AD defined Domain and they would
> automatically have the correct database rights.
> Questions:
> - Is the scenario that I am describing possible to implement?
> - If so is there a source of information someone can point me at?
> Thanks,
> -Anthony
>|||Hi Uri - Thanks for the pointer. However, it still isn't clear to me what I
need to do.
What I'm really trying to do is manage the database roles through AD.
Ideally I would like to use Windows Authentication to access the SQL server
and then have that same set of Windows credentials automatically define the
user’s rights within the database.
In other words I don’t want to administrate each individual user’s right
s at
the database. Instead I want to set up my SQL server to say any member of my
casual-users group can edit table X. Then I would use AD to define what user
s
are in the casual-users group.
Thanks,
-Anthony
"Uri Dimant" wrote:

> Anthony
> http://vyaskn.tripod.com/sql_server...t_practices.htm --s
ecurity
> best practices
>
> Also , you may want to look into a database role to manage users rights
>
>
> "Anthony" <Anthony@.discussions.microsoft.com> wrote in message
> news:BEDEB701-EF66-4827-8D3A-F79F6C7EA284@.microsoft.com...
>
>|||At a high level, you would add the two Windows groups as
logins to SQL Server. You would add these two logins you
just added as users in the database. You would set the
appropriate permissions on these windows groups you just
added as database users. Moving the users in and out of the
AD groups at the AD level then flows down to the members of
the Windows group that you just added as logins (and then
database users). If you user is added to the AD group that
has the login and permissions in the database, that user
gets the login and permissions in the database based in
their membership to the windows. The windows group has the
permissions on SQL Server so moving users in and out of that
Windows group is what would control their access to SQL
Server and the database.
-Sue
On Mon, 13 Feb 2006 12:41:22 -0800, "Anthony"
<Anthony@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Hi Uri - Thanks for the pointer. However, it still isn't clear to me what I
>need to do.
>What I'm really trying to do is manage the database roles through AD.
>Ideally I would like to use Windows Authentication to access the SQL server
>and then have that same set of Windows credentials automatically define the
>users rights within the database.
>In other words I dont want to administrate each individual users rights a
t
>the database. Instead I want to set up my SQL server to say any member of m
y
>casual-users group can edit table X. Then I would use AD to define what use
rs
>are in the casual-users group.
>Thanks,
>-Anthony
>
>"Uri Dimant" wrote:
>