Get Email Address from UserName

[jmeckley]

I am looking for a way to get a user's email address from their username. I would get their user name from the sysusers table on the database (SQL Server 2000) Here is an example of what I am talking about:

Example:
sysuser table (not all columns are shown)
ID UserName
1 jmichaels
2 tsmith
3 djones

through stored procs and ASP I would like to get their email address
jmeckley = [email protected]
tsmith = [email protected]
djones = [email protected]

I have added the ActiveDS component to my current project and added Import ActiveDS to the class and the code in the subroutine looks like this:
Dim usr As IADsUser
Dim fulName As String
Set usr = GetObject("WinNT://Microsoft/JSmith,user")
fulName = usr.EmailAddress

The GetObject takes 2 optional arguements
1. File Path
2. Class
If you don't pass a file path you have to pass a class. The string in the GetObject function above came directly from the help file. I do not know what that text means. I do not know if, how, or where to get the active directory file for the user selected from the sysuser table.

Does anyone know if this is possible for 1 user to access another users profile to get the email address so they can send them an email? Thank you for any insight provided on this topic.

[jmeckley]

I got it to work! I can access Active Directory and get any piece of information I need from it.

I use Sql Server and .Net.

1. Add Active Directory to Sql Server
sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
ADSI is the name you use to reference Active Directory when you want to use it.

2. Create a view for easy access
select * from OpenQuery( ADSI,'<LDAP://W2KRoot>;(&(objectCategory=Person)(objectClass=user));name, adspath;subtree')
Name and ADsPath are the fields you want to include in the view, I cannot find any other field to add from AD.

3. Write a stored procedure to return the ADsPath
Create Proc sp_ActiveDirectory_s
@User as char(20)
AS
Select ADsPath
From ADUser_v
Where name = @User

4. Add a reference (Menu Project -> Reference) to the COM object called Active DS Type Library

5. Write code to access Active Directory
Imports ActiveDS
...
Dim usr As IADsUser
Dim ActDir as String
Dim Email as String

ActDir = 'call sp_ActiveDirectory_s
usr = GetObject(ActDir)
Email = usr.EmailAddress

There are many more fields from AD that you can access once usr is set. Title, Address, Groups, etc can all be accessed.

I hope this helps some of you out there. Active Directory is a very power tool. If you see anything that can be expanded upon or more effecient please post.