Active directory users AND memberships

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

General SQL Server Forums

New to SQL Server Programming

Active directory users AND memberships

Author

Topic

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-17 : 10:20:09

I am trying to make all reports on SSRS location specific. e.g I only people who work in certain locations will be able to view certain parameters.

I figured that I can do this using User!User.ID and an access control list. I have tested this by manually creating a sample ACL but I want to be able to automatically populate the list with data from AD.

I can find lots of stuff online that allowed me to extract information from AD but I cannot see a way of creating a full list which includes memberships.

e.g:-
USERNAME MEMBER OF
User1 location1
User1 location2
User1 location3
User2 location1
User2 location4

I am able to get users by specifying a group and getting memberships by specifying a user but I am not able to get a full list.

Does anyone know if this is possible? I have created a ADSI link server.

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-17 : 10:45:43

These are the individual queries

This one to list all users:

SELECT sAMAccountname AS AccountName, GivenName AS [First Name], SN AS [Last Name],
mail AS [E-mail Address],
TelephoneNumber AS [Telephone Number], l AS Office,
PostalAddress AS [Postal Address], PostalCode, StreetAddress AS [Street Address],
wWWHomePage AS [Web Page], distinguishedname AS DN
FROM OPENQUERY(ADSI,
'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage
FROM ''LDAP://dc=xxxxx,dc=local''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
ORDER BY Name')
Rowset_1

And this one to list all groups

SELECT sAMAccountname AS AccountName, GivenName AS [First Name], SN AS [Last Name],
mail AS [E-mail Address],
TelephoneNumber AS [Telephone Number], l AS Office,
PostalAddress AS [Postal Address], PostalCode, StreetAddress AS [Street Address],
wWWHomePage AS [Web Page], distinguishedname AS DN
FROM OPENQUERY(ADSI,
'SELECT GivenName, SN, company, department, TelephoneNumber, mail, distinguishedname, title, l, manager, mobile, name, PhysicalDeliveryOfficeName, PostalAddress, PostalCode, sAMAccountname, StreetAddress, wWWHomePage
FROM ''LDAP://dc=xxxxxx,dc=local''
WHERE objectCategory = ''group'' AND objectClass = ''group''
ORDER BY Name')
Rowset_1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources