May 10, 2008 at 11:24 am
I have searched the web and can't find a step by step solution on how to use Active Directory group membership as a method of restricting/allowing access to SQL 2005 tables/columns.
Can anyone help?
Mike.
May 12, 2008 at 7:03 am
You can give permissions (add a login to your server) through SQL Logins, or through NT Authenticated logins. An NT Authenticated login can be a user or a group from AD. So, add an AD group as a login, give the login the appropriate database and object permissions, and add users to the AD group. When they access the database with NT Authentication, they will have the assigned permissions.
May 12, 2008 at 8:07 am
There is actually no difference to set up an AD account in the database to a SQL Login when it comes to groups/roles.
Hopefully you were given rights to the SQL Server OU which allows you to add and set up AD group, (users should always be set up by the network admin people and once set up, then you add them to the SQL Server OU).
What I've found work best, is to create various groups, I normally do something like databasename/role and then assign that role login rights and role rights on SQL Server. In my experience, AD has actually been a godsend when we comes to SQL Server. Especially when the IT Director comes and say this person has been terminated and needs to be locked out. With AD, once their user account is deleted, their access to any AD Group is deleted.
Marvin Dillard
Senior Consultant
Claraview Inc
May 12, 2008 at 9:41 am
Is the SQL Server OU setup by ADMIN or by the SQL server installation process?
Under SQL Management, where do you actually pick the AD User/Group during the SQL Login create process?
May 12, 2008 at 9:56 am
The SQL Server OU is part of Active Directory, so you have to check with your network admins to get it set up. ONce set up, they should give you keys to control it.
Then when you set up new users in SQL Server, you simply check NETWORK instead of SQL Server login and that will provide the members of the OU login rights.
The best way to think about AD groups is to relate them to a DB role. You assign user's to roles, and they have the access rights of the role. In AD you assign users to groups and those groups to SQL Server. That way, when a new member needs to join the group, you simply add the person to the group and they automagically have all rights of the group.
Marvin Dillard
Senior Consultant
Claraview Inc
May 12, 2008 at 11:43 am
there is no SQL OU by default. i called mine SQL Access
we create groups and then map sql logins to those groups. then either give db_reader role on replicated copies or table access for RW on master copies
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply