|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Rights QuestionWe added the entire domain group ICT as login. So at the Logins view in Enterprise Manager is shows DOMAIN\ICT. The group ICT in the domain contains three (3) users. One of the users is JTB. Now we have a database called lets say Production. We added a user ICT based on Login DOMAIN\ICT. Now we have a VB6 program which connects to the SQLServer Database using a connection string without a username, because we use Windows Authentication. In the VB6 program I know it is user JTB, but now I need the domain group he is in (in this case ICT), to know his rights on the Database for each view or table. Questions: - Are we doing this the right way by adding domain groups as SQLSever Database Users - How do I get to know the domain group for a ceratin user. Any anwsers or suggestions? Thx Bart I often see 2 different ways to do rights as you need.
1. using NT groups - we certainly do NOT want to get into permissions for each individual. 2. using a standard SQL login which the program knows. The user logs into the program and the program provides whatever security is necessary by showing/hiding menu items, etc. In this case the running program always has all the rights it needs to do anything for anyone, but restricts the user. In any case, get LDAP information in SQL 2000 you will have to write an LDAP query or use some script. Search on the net for SQL LDAP Query and you will get some examples.. The user will be a member of many groups, not just the one you are interested in - so be prepared for that. In SQL 2005 you could simply SELECT * FROM sys.login_token and you';d get a listing of all of the NT groups, and SQL roles this login was a member of... neat!@ -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Bart Steur" wrote: > We have setup our SQLServer 2000 as follows: > > We added the entire domain group ICT as login. So at the Logins view in > Enterprise Manager is shows DOMAIN\ICT. The group ICT in the domain contains > three (3) users. One of the users is JTB. > > Now we have a database called lets say Production. We added a user ICT based > on Login DOMAIN\ICT. > > Now we have a VB6 program which connects to the SQLServer Database using a > connection string without a username, because we use Windows Authentication. > > In the VB6 program I know it is user JTB, but now I need the domain group he > is in (in this case ICT), to know his rights on the Database for each view > or table. > > Questions: > > - Are we doing this the right way by adding domain groups as SQLSever > Database Users > - How do I get to know the domain group for a ceratin user. > > Any anwsers or suggestions? > > Thx > > Bart > > > > I use Windows integrated authentication. I create a role in the database
that has permissions for all the tables, stored procedures, functions, etc. that I want. Then I create a user, which is a domain group, and assign this group the role. Every member of the group has the permissions of the role. You could have a separate role for each group. In VB you can use ADSI to determine the groups a user is a member of. Better yet, you can check if the user is a direct member of a specified group (not due to group nesting). A brief VBScript example that can be easily converted to VB: ' Retrieve Distinguished Name of the current user. Set objSysInfo = CreateObject("ADSystemInfo") strUserDN = objSysInfo.UserName ' Specify the Distinguished Name of the group. strGroupDN = "cn=ICT,ou=Sales,dc=MyDomain,dc=com" ' Bind to the group object. Set objGroup = GetObject("LDAP://" & strGroupDN) ' Check if the user is a direct member of this group. If objGroup.IsMember("LDAP://" & strUserDN) Then ' The user is a direct member of the group. Else ' The user is not a direct member of the group. End If Show quote "Wayne Snyder" <wayne.nospam.snyder@mariner-usa.com> wrote in message news:A43AB0E7-5BB1-4EF6-B7DC-B61331C1B4C5@microsoft.com... >I often see 2 different ways to do rights as you need. > 1. using NT groups - we certainly do NOT want to get into permissions for > each individual. > 2. using a standard SQL login which the program knows. The user logs into > the program and the program provides whatever security is necessary by > showing/hiding menu items, etc. In this case the running program always > has > all the rights it needs to do anything for anyone, but restricts the user. > > In any case, get LDAP information in SQL 2000 you will have to write an > LDAP > query or use some script. Search on the net for SQL LDAP Query and you > will > get some examples.. The user will be a member of many groups, not just the > one you are interested in - so be prepared for that. > > In SQL 2005 you could simply > > SELECT * FROM sys.login_token > and you';d get a listing of all of the NT groups, and SQL roles this login > was a member of... neat!@ > -- > Wayne Snyder MCDBA, SQL Server MVP > Mariner, Charlotte, NC > > I support the Professional Association for SQL Server ( PASS) and it''s > community of SQL Professionals. > > > "Bart Steur" wrote: > >> We have setup our SQLServer 2000 as follows: >> >> We added the entire domain group ICT as login. So at the Logins view in >> Enterprise Manager is shows DOMAIN\ICT. The group ICT in the domain >> contains >> three (3) users. One of the users is JTB. >> >> Now we have a database called lets say Production. We added a user ICT >> based >> on Login DOMAIN\ICT. >> >> Now we have a VB6 program which connects to the SQLServer Database using >> a >> connection string without a username, because we use Windows >> Authentication. >> >> In the VB6 program I know it is user JTB, but now I need the domain group >> he >> is in (in this case ICT), to know his rights on the Database for each >> view >> or table. >> >> Questions: >> >> - Are we doing this the right way by adding domain groups as SQLSever >> Database Users >> - How do I get to know the domain group for a ceratin user. >> >> Any anwsers or suggestions? >> >> Thx >> >> Bart >> >> >> >>  |
|||||||||||||||||||||||
Other interesting topics