Query ActiveDirectory to receive users of group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query ActiveDirectory to receive users of group

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-12-28 : 07:46:34

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 - 2012-12-31 : 09:08:37

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources