August 7, 2003 at 5:03 am
Hi,
I am tring to set up my database to use NT(W2k & SQL2000) Security. I have about 5 departments who all need different rights on the database objects. Eg only finance can view financial data etc.. The way I see it I need to setup a custom role for each of these groups of people within my database and assign permissions on the objects to the role. Then create a login for each person and assign access to the database under the correct role for their department. Is this correct so far?
Where do windows groups come in though?
I can add windows groups as logins, so presumably every person who is a member of the group can then access the server? would I then add the group login to my database and assign the correct custom role as before to give permissions? If this works then I would not need to add new users at all to the database, as when a new person comes to the company they would be given an NT account and added to their departmental W2K group by the people who look after the network. So it would be a real bonus for me if I can do this. I do however need to be able to tell the users of a group apart once they are in the database as I have an Auditing capability that will need to get the NT login name of the user.
Does anyone know if I on the right track with this?
Thanks in advance.
August 7, 2003 at 5:14 am
Hi cwedgwood50,
quote:
I am tring to set up my database to use NT(W2k & SQL2000) Security. I have about 5 departments who all need different rights on the database objects. Eg only finance can view financial data etc.. The way I see it I need to setup a custom role for each of these groups of people within my database and assign permissions on the objects to the role. Then create a login for each person and assign access to the database under the correct role for their department. Is this correct so far?Where do windows groups come in though?
I can add windows groups as logins, so presumably every person who is a member of the group can then access the server? would I then add the group login to my database and assign the correct custom role as before to give permissions? If this works then I would not need to add new users at all to the database, as when a new person comes to the company they would be given an NT account and added to their departmental W2K group by the people who look after the network. So it would be a real bonus for me if I can do this. I do however need to be able to tell the users of a group apart once they are in the database as I have an Auditing capability that will need to get the NT login name of the user.
some time ago I asked a very similar question. It might help you getting started http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=14632
I haven't managed to change my environment yet, so I can't tell whether you are able to identify the single within an NT Group.
I guess you are, but test it before
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 7, 2003 at 8:42 am
Hi Frank
Thanks for that. Just trying to sound out how other people are doing this. It looks like we have a similar need. I have been able to get the user by using 'select system_user', but prototype is still in testing.
Regards
Chris
August 7, 2003 at 11:27 pm
Hi Chris,
quote:
Thanks for that. Just trying to sound out how other people are doing this. It looks like we have a similar need. I have been able to get the user by using 'select system_user', but prototype is still in testing.
that sounds good.
Could you post the results here once you have finished testing?
Cheers,
Frank
Edited by - a5xo3z1 on 08/07/2003 11:28:30 PM
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
August 8, 2003 at 12:10 pm
Hi Frank,
Unrelated issues with the front-end mean that it will be quite a while before I have an end-to-end solution, but I had quite a bit of success with this by using QA to simulate requests from the front-end.
I performed the following tests:
-In NT Added a new windows user (no groups assigned)
Added the login for this user in SQL
Added the user to my prototype database with datareader fixed roll.
Ran a select in QA - data returned.
removed datareader roll
ran query - no permission
Created a custom roll with select permission on the same table as the query.
assigned custom roll to user
-ran query - data returned
OK straightforward so far, now we get interesting:
removed all trace of the user & login from the DB
Added 2 NT security groups
made the user a member of 1
assigned that group to custom role in DB
ran query - data returned
ran select system_user login of the user (not the group) returned.
Added user to the other NT group and performed the same actions.
As expected got same results.
Gave the other group deny permissions - user denied access, so denied overrides!
So just to recap - the user could gain access to my database by virtue of belonging to an NT group with sufficient privilage and be identified to the user, despite not having a login of their own in sql.
oh yeah, I also added back the users login and used this to give additional privilage(like where a manager wants more access than his team), and this worked fine too. It was always the sum of the privilage (with deny overiding) that was taken.
I think it's definately a usable solution, although for other reasons I am not sure if this is what we will end up implementing.
Regards
Chris
August 14, 2003 at 8:46 pm
What you are trying to do is perfectly acceptable. NT groups can be defined to sql server and then nt login management is at the NT end. To assist in your auditing request try the following extended stored procs
xp_enumgroups
xp_logininfo 'domain\login', 'all'
The first provides groups on the local server, you can specify domain server if required. The second lists login and nt groups they have privs to. These two procs can be executed from sql query analyser.
Regards,
Derek
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply