SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query ActiveDirectory to receive users of group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/28/2012 :  07:46:34  Show Profile  Reply with Quote
I just can't get this one to work:

DECLARE @TSQL varchar(8000), @dn char(500)
SET @dn = 'LDAP://cn=testi_cc,ou=Users,dc=mycompany,dc=fi'
SET @TSQL = 'SELECT givenName, sn as lastName FROM OPENQUERY( ADSI, ''SELECT givenName, sn FROM ''''' + @dn + ''''''')'
EXEC (@TSQL)


So I have a group testi_cc and a member of that is testi_cc_tr1 and I'm a user of that testi_cc_tr1. I can't hardcode this testi_cc_tr1 there, because that would ruin the idea.

Basically I would want a recursive query which lists for a given group every user member, no matter how deep in groups they are. So if User X is a member of a group A which is a member of a group B which is a member of a group C which is a member of a group D which is a member of a group E, then I still would get this User X as a result from some kind of
SELECT users
FROM group E

query

KilpAr
Yak Posting Veteran

80 Posts

Posted - 12/31/2012 :  09:08:37  Show Profile  Reply with Quote
Here's what I posted to one other forum:

Hi,

I have a VBA-program that uses SQL Server as database and ActiveDirectory (in Windows Server 2008 R2) as user authentication and I'm having big time problems with getting the LDAP-part there to work.

This is a program for a bigger corporation - several companies that are not allowed see the data of other companies - and each company has their own projects of which each project uses this program. I need to be able to have a simple function that takes as parameters the userID (i.e. ActiveDirectory user) and the project the user is trying to access. Both of those are easily done with VBA. The function needs to return the level of access the user got: No Access, Read Only or Full Access. Yes, I'm aware that VBA is relatively easy to crack, but let's put that part aside, the users here are not very advanced and the info is not _that_ secret anyway.

While I could implement this with making two groups (Full Access and Read Only) for each project, I have here also users like IT sector and financial sector and the board of directors that needs to have full access on every project there is. I also have company leaders that can only access the info of their own company. This still could work as just putting each user into each project they have access.

Now clearly I could make this kind of groups:
-Corporation full access (read and write every single project there is)
-Corporation read only (read every single project there is, write to none)
-Company A full access (read and write to Company A projects, no access to Company B projects)
-Company A read only (read Company A projects, write to none)
-Company B full access (read and write to Company B projects, no access to Company A projects)
-Company B read only (read Company B projects, write to none)
-Project A1 full access (read and write to Project 1 of Company A, no access to other projects)
-Project A2 full access (read and write to Project 2 of Company A, no access to other projects)
-Project B1 full access (read and write to Project 1 of Company B, no access to other projects)
-Project B2 full access (read and write to Project 2 of Company B, no access to other projects)
-Project A1 read only (read Project 1 of Company A, no access to other projects)
-Project A2 read only (read Project 2 of Company A, no access to other projects)
-Project B1 read only (read Project 1 of Company B, no access to other projects)
-Project B2 read only (read Project 2 of Company B, no access to other projects)

but how do I make an LDAP-query that gets as parameters
1) The name of user, say, the owner of the whole corporation
2) The project, say, Project5G (company 5, project 7)
and returns as answer "He has full access to it".

I would need the answer in that kind of form that I can embed it to either
1) VBA code
or
2) SQL Server as T-SQL code.

For VBA the function could look this
Function AuthenticateUser(strUser As String, strProject As String) As Integer
'And to here the LDAP-code that I'm asking for here
End Function

For SQL Server / T-SQL I have already a linked server so I can query and receive info of each user. Like this:

SELECT sAMAccountName
FROM (
SELECT sAMAccountName
FROM OPENQUERY(
ADSI, '
SELECT sAMAccountName
FROM ''LDAP://OU=MyOrganizationUnit,DC=MyCorporationName,DC=com''
WHERE objectClass <> ''computer''
'
) AS sqone
) AS sqtwo
ORDER BY sAMAccountName

I can also easily mix and match VBA and T-SQL code, so as long as I get that LDAP-code, I think I can manage.

But the hard part for me here is that while I can ask that is a user a part of a certain group, I have no idea how to do it for nested groups. So while I know that person X is a part of Company A board and thus should have the full access to project Project1A, I can only receive the info that he is a part of the board of Company A and that's it. So I don't know how to extend the query to return also that Company A board has a full access to all Company A projects => person X has "indirectly" access to Project1A (not by being person X, but by being a part of the board of Company A).



Also, if you feel like there's some simpler and/or better solution, I'm totally open to hear it.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000