Active Directory Groups vs. using SQL Server Groups.

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