Page 2 of 4

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Fri Jan 23, 2015 2:57 pm
by Maries
Thank you all, I apologize for the bad post!

The script that I have is part of a SQL job. The job runs the script and I didn't realize that the entire script wasn't posted.

I'm going to update the post, I was trying to edit it, but I don't see that option.

I am new to VB and also SQL jobs that run scripts, so I appreciate the help.

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Sat Jan 24, 2015 10:32 am
by jvierra
How to use a direct AD query as a linked server in MSSQLServer.

This is the T-SQL script that sets up your linked server. Using this you can query and update tables using a simple task trigger. This is more secure and much faster than using the very old VBScript methods.

Code: Select all

--Create a Linked Server 
EXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

--Configure the server to allow OPENQUERY functions 
sp_configure 'show advanced options', 1 
reconfigure with override

sp_configure 'Ad Hoc Distributed Queries', 1 
reconfigure 

#EXEC sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'true'

SELECT * FROM OpenQuery(
ADSI, 'SELECT * FROM ''LDAP://DC=<your domain>,DC=<your domain>'' WHERE objectCategory=''User''')

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Wed Jan 28, 2015 7:58 pm
by Maries
Thank you again! Here are the details:

1- This script runs within a SQL job on a 2008 sql server.
2- It retrieves AD data and populates a SQL DB with that data
3- It functions fine using the entire code below, this was written by a SQL DBA a few years ago and I was able to add a couple user attribs to it.
4- Now the request is to add the sAMAccountName of each users Manager to the list of items populated in the database. I am not sure how to retrieve that as I can get the Managers DN, but that's easy as I have found.

the code is below, any help would be very appreciated

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Wed Jan 28, 2015 8:03 pm
by Maries
I couldn't get the code to paste as the forum kept prompting there were to many off-site URLs for new users..I tried removing everything I could find, but no luck, so I attached it as a text doc.

Thx!
Marie

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Wed Jan 28, 2015 8:10 pm
by jvierra
I showed you how to get the manager. I don't understabd what you are asking for now.

just use the code to get the manager.

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Mon Feb 02, 2015 9:43 am
by Maries
I added the code to the script, but the manager doesn't get added to the list of attributes populated in the database.

Can you tell me where in the script the code has to go and also I assume I need to add in a line that adds the name to the sql record set?

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Mon Feb 02, 2015 12:47 pm
by jvierra
The managers field is likley not used on most objects. Have you looked in AD to see if it has been set.

I do not see in your code where you have assigned the manager.

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Mon Feb 02, 2015 12:51 pm
by jvierra
In your code you would do this:
VBScript Code
Double-click the code block to select all.
Set mgr = GetObject("LDAP://" & rs.Fields("manager") )
msgbox mgr.SamAccountName

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Mon Feb 02, 2015 1:34 pm
by Maries
I should have been clearer. When I run that code you provided, it does gather the managers samaccountname, but I have no idea where I would add that code to the script I posted in order for the managers samaccountname to get added to the list of attributes that are populated to the database.

The message box isn't going to help me, this is supposed to run as a scheduled job that involves no interaction.

Thanks for the help though, I'll do something else.

Re: Help with VB Script - Get the Managers sAmAccountName?

Posted: Mon Feb 02, 2015 1:48 pm
by jvierra
So you are saying you don't know how to do simple assignment in VBScript?

Just assing the field to the results of the mgr.SAmaccountName

rs.Field("manager") = mgr.SamAccountName