Active Directory Groups vs. using SQL Server Groups.

[Hoardling1 via SQLMonster.com]

Which is better in use. Setting up Active Directory groups and using it to
guide your security in the sense that you create a read and a read/write
group and just move users in and out of these security groups and manage
security for SQL Server from Active Directory. Or use SQL Server and move
all users into security and create groups in SQL Server and manage the
security from there. If you choose one can you explain why you chose that
setup. I have been discussing with my Network Admin and he wants Active
Directory security, while I want more in SQL Server, so we are at a
difference. I am trying to find out the pros and cons of each.
Thanks.

--
Message posted via http://www.sqlmonster.com

[Dan Guzman]

I think the best solution is to leverage both AD and SQL Server role-based
security. This reduces the ongoing security administration effort. The
approach I usually use:

One-time setup tasks:

- Create database roles (e.g. SalesManagers)

- Grant object permissions to database roles (e.g. GRANT EXECUTE ON
dbo.uspGetSalesQuota TO SalesManagers)

- Create AD groups based on organizational roles (e.g.
MyDomain\SalesManagers)

- Create SQL Server logins for the AD groups (e.g. CREATE LOGIN
[MyDomain\SalesManagers] FROM WINDOWS)

- Create database users for the AD groups (e.g. CREATE USER
[MyDomain\SalesManagers])

- Add database users to the roles (e.g. EXEC sp_addrolemember
'SalesManagers', 'MyDomain\SalesManagers')

Going forward, your Network Admin can then control security via AD group
membership. You can also accomplish this without the SQL roles (i.e. grant
permissions directly to AD groups) but that requires that you have a
different object permission script for each environment (dev, QA,
production, etc.). I've found it easier to use the same database object
security for all database instances, with the only environmental difference
being logins, users and role membership.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Hoardling1 via SQLMonster.com" <u43783@uwe> wrote in message
news:ad2aa9732d5a8@uwe...

[Hoardling1 via SQLMonster.com]

Interesting, I will look into this process and see how well it will fit in
our environment.

Thank You.

Dan Guzman wrote:
>> Which is better in use. Setting up Active Directory groups and using it
>> to

>[quoted text clipped - 6 lines]

>> Directory security, while I want more in SQL Server, so we are at a
>> difference. I am trying to find out the pros and cons of each.
>
>I think the best solution is to leverage both AD and SQL Server role-based
>security. This reduces the ongoing security administration effort. The
>approach I usually use:
>
>One-time setup tasks:
>
>- Create database roles (e.g. SalesManagers)
>
>- Grant object permissions to database roles (e.g. GRANT EXECUTE ON
>dbo.uspGetSalesQuota TO SalesManagers)
>
>- Create AD groups based on organizational roles (e.g.
>MyDomain\SalesManagers)
>
>- Create SQL Server logins for the AD groups (e.g. CREATE LOGIN
>[MyDomain\SalesManagers] FROM WINDOWS)
>
>- Create database users for the AD groups (e.g. CREATE USER
>[MyDomain\SalesManagers])
>
>- Add database users to the roles (e.g. EXEC sp_addrolemember
>'SalesManagers', 'MyDomain\SalesManagers')
>
>Going forward, your Network Admin can then control security via AD group
>membership. You can also accomplish this without the SQL roles (i.e. grant
>permissions directly to AD groups) but that requires that you have a
>different object permission script for each environment (dev, QA,
>production, etc.). I've found it easier to use the same database object
>security for all database instances, with the only environmental difference
>being logins, users and role membership.
>

>> Which is better in use. Setting up Active Directory groups and using it
>> to

>[quoted text clipped - 7 lines]

[og1]

Dan Guzman wrote on 08/28/2010 11:11 ET :
>> Which is better in use. Setting up Active Directory groups and using it
>> to
>> guide your security in the sense that you create a read and a read/write
>> group and just move users in and out of these security groups and manage
>> security for SQL Server from Active Directory. Or use SQL Server and move
>> all users into security and create groups in SQL Server and manage the
>> security from there. If you choose one can you explain why you chose that
>> setup. I have been discussing with my Network Admin and he wants Active
>> Directory security, while I want more in SQL Server, so we are at a
>> difference. I am trying to find out the pros and cons of each.
>>
>>
>>
> I think the best solution is to leverage both AD and SQL Server role-based
> security. This reduces the ongoing security administration effort. The
> approach I usually use:
>
> One-time setup tasks:
>
> - Create database roles (e.g. SalesManagers)
>
> - Grant object permissions to database roles (e.g. GRANT EXECUTE ON
> dbo.uspGetSalesQuota TO SalesManagers)
>
> - Create AD groups based on organizational roles (e.g.

> MyDomainSalesManagers)

>
> - Create SQL Server logins for the AD groups (e.g. CREATE LOGIN

> [MyDomainSalesManagers] FROM WINDOWS)

>
> - Create database users for the AD groups (e.g. CREATE USER

> [MyDomainSalesManagers])

>
> - Add database users to the roles (e.g. EXEC sp_addrolemember

> 'SalesManagers', 'MyDomainSalesManagers')

>
> Going forward, your Network Admin can then control security via AD group
> membership. You can also accomplish this without the SQL roles (i.e. grant
> permissions directly to AD groups) but that requires that you have a
> different object permission script for each environment (dev, QA,
> production, etc.). I've found it easier to use the same database object
> security for all database instances, with the only environmental difference
> being logins, users and role membership.
>

> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>

> "Hoardling1 via SQLMonster.com" wrote in message
> news:

>> Which is better in use. Setting up Active Directory groups and using it
>> to
>> guide your security in the sense that you create a read and a read/write
>> group and just move users in and out of these security groups and manage
>> security for SQL Server from Active Directory. Or use SQL Server and move
>> all users into security and create groups in SQL Server and manage the
>> security from there. If you choose one can you explain why you chose that
>> setup. I have been discussing with my Network Admin and he wants Active
>> Directory security, while I want more in SQL Server, so we are at a
>> difference. I am trying to find out the pros and cons of each.
>> Thanks.
>>

>> Message posted via http://www.sqlmonster.com
>>
>

This looks like a well thought of solution.
However, once this is set up, is it possible to identify a specific member of
the group?
In other words, if John Doe is a member of windows group group_02 and group_02
is also a SQL Server login, what happens when you query 'select SYSTEM_USER'?
I
think the reslut will be 'group_02' and there will be no way of knowing that
John Doe is the one who is actually using the database.
If this is correct, how can one audit database usage and still rely on
solution
such as yours?

Thanks in advance,
Ofer