May 10, 2011 at 7:50 am
If I have an AD group named "Domain\Sales" that contains several domain users (e.g. "Domain\jjones" and "Domain\jsmith") and I want the entire department to have the same specific permissions to a SS2K8 database, what would be the best way to do that? Here's what I'm thinking, but it may not make the most sense:
1) Create a SQL Server login for "Domain\Sales" with user "Domain\Sales"
2) Create database role with specific permissions
3) Add user "Domain\Sales" to database role
Ultimately, I want "Domain\jjones" or any other user under "Domain\Sales" to be able to log in to the server and pick up the permissions of the database role I created. I've been trying to read up on SS security, but when it comes to security based on AD groups, I get a little lost and don't understand if I need to create logins for each individual or if individuals will be able to log in and get the appropriate security by virtue of being in the "Domain\Sales" AD group.
Any help is appreciated.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 10, 2011 at 8:00 am
First, make sure your instance is in Mixed Authentication mode.
After this is done, you can create a new login specifying a windows account (or group). Search for the windows group. Leave them with a public server role.
In the user mapping section, check the box next to the database you want them to have access to. With that database selected, assign appropriate permissions to the windows group (datareader only?)
When this is done, the users will log in using windows authentication and should only have read only access to that one database.
May 10, 2011 at 8:09 am
calvo (5/10/2011)
First, make sure your instance is in Mixed Authentication mode.After this is done, you can create a new login specifying a windows account (or group). Search for the windows group. Leave them with a public server role.
In the user mapping section, check the box next to the database you want them to have access to. With that database selected, assign appropriate permissions to the windows group (datareader only?)
When this is done, the users will log in using windows authentication and should only have read only access to that one database.
OK, so using my example,
Domain\Sales would get added as a login and user with the default public server role.
I'd then give the Domain\Sales user access to the appropriate databases and assign the user a role (you mentioned datareader, but it could be a database role with very specific permissions, right?)
After all of that, Domain\jjones would be able to log in to the server successfully (because he belongs to the Domain\Sales AD group), and his permissions would be carried out to the database level because Domain\Sales has permissions on the database up to the point that its role will allow (datareader fixed role or a custom database role)
Is that all correct?
Mike Scalise, PMP
https://www.michaelscalise.com
May 10, 2011 at 8:20 am
If you dont want to require an audit and specific user actions then create SQL server role for the specific group as SALES and create sales user and assign this role to this sales user
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 8:39 am
mikes84 (5/10/2011)
OK, so using my example,Domain\Sales would get added as a login and user with the default public server role.
I'd then give the Domain\Sales user access to the appropriate databases and assign the user a role (you mentioned datareader, but it could be a database role with very specific permissions, right?)
After all of that, Domain\jjones would be able to log in to the server successfully (because he belongs to the Domain\Sales AD group), and his permissions would be carried out to the database level because Domain\Sales has permissions on the database up to the point that its role will allow (datareader fixed role or a custom database role)
Is that all correct?
That is correct.
The user domain\jjones would inherit access and permissions given to the AD group. So any permissions you assign to the domain\sales group, will be given to users in that group.
May 10, 2011 at 10:41 am
Syed Jahanzaib Bin hassan (5/10/2011)
If you dont want to require an audit and specific user actions then create SQL server role for the specific group as SALES and create sales user and assign this role to this sales user
The information in your post is less than useful because it is unclear whether you are referring to the AD Group as a Server Principals (i.e. Login) or a Database Principal (i.e. User). Also, there is not a specific entity in SQL Server named "SQL server role"...the term is generic and ambiguous.
You cannot "create SQL server role for the specfic group" ... you can create a Server Login for the AD Group ... and then create a Database User for the Server Login ... and then create a User-Defined Database Role to eventually contain the Database User ... and then grant all permissions to the User-Defined Database Role. Is that what you meant?
There are Fixed Server Roles (e.g. sysadmin) which can contain Server Principals, however until Denali is released we cannot create User-Defined Server Roles. At the Database Level we have Fixed Database Roles, User-Defined Database Roles and Application Roles...all of which are containers for Database Principals.
Please stop rushing to post any idea that pops into your mind, read the original post carefully, and make an attempt to help the people taking the time to learn their craft...otherwise don't post anything because your chronically incomplete and ambiguous entries do more harm than good. Keep in mind too that search engines are major drivers of traffic to the site so by posting bad info you're liable to adversely impact many more people than just the original poster...either that or stick to something you know a little better...based on your signature it may be that you're more qualified to cruise IBM DB2 or Oracle sites and help users there. Maybe you should be reading more posts on this site for the time being.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 10, 2011 at 10:53 am
If you are working with AD permissions and groups, you don't need mixed mode. Windows Auth only is sufficient.
May 10, 2011 at 12:05 pm
Steve Jones - SSC Editor (5/10/2011)
If you are working with AD permissions and groups, you don't need mixed mode. Windows Auth only is sufficient.
^^ what he said
May 10, 2011 at 12:05 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply