AD username sync to table - SQL Server Forums

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

AD username sync to table

Author

Topic

Adam_231
Starting Member

4 Posts

Posted - 2012-03-20 : 06:45:01

Hi,

I am new to SQL, so please excuse my ignorance around this.

What I am hoping for is to be able to syncronise from AD the list of usernames to a column of a table in SQL2008. I have created already the table and column however I am unsure if it is possible to script/automate the sync I am looking for, any advice/direction would be greatly appreciated.

Adam

_____________________

Adam

Adam_231
Starting Member

4 Posts

Posted - 2012-03-20 : 09:41:56

hmm ok, got a bit further myself;

I've got SQL connected to AD via the OLE DB Provider for Microsoft Directory Services

I've also got an SQL query which returns the result I need;

SELECT samAccountName

FROM OPENQUERY(ADSI,

'SELECT samAccountName, givenName

FROM ''LDAP://DC=testdomain,DC=local''

WHERE objectClass=''Person''

AND objectClass = ''User''')

WHERE givenName IS NOT NULL

What I need to understand now is how I get that information put in the correct column of an already created table?

_____________________

Adam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-20 : 09:52:28

INSERT INTO <your table here>
(<column list here>)
<your select query here>

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Adam_231
Starting Member

4 Posts

Posted - 2012-03-20 : 11:07:30

DonAtWork,

Many thanks for your reply.

Apologies for asking what might seem a stupid question but I'm fumbling around in the dark a little here trying to get this to work as I'm certainly not a DBA.

Do I append my current query with this? Also, for the table name, do I need to prefix with dbo. or just the tablename? For example should it look like this;

___________________________

SELECT samAccountName

FROM OPENQUERY(ADSI,

'SELECT samAccountName, givenName

FROM ''LDAP://DC=testdomain,DC=local''

WHERE objectClass=''Person''

AND objectClass = ''User''')

WHERE givenName IS NOT NULL

INSERT INTO dbo.UserAccessCards
(userName)
samAccountName
________________________________________________

Thanks in advance

Adam

_____________________

Adam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-20 : 13:51:08

[code]
INSERT INTO dbo.UserAccessCards
(userName)
SELECT samAccountName

FROM OPENQUERY(ADSI,

'SELECT samAccountName, givenName

FROM ''LDAP://DC=testdomain,DC=local''

WHERE objectClass=''Person''

AND objectClass = ''User''')

WHERE givenName IS NOT NULL
[/code]

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Adam_231
Starting Member

4 Posts

Posted - 2012-03-21 : 06:10:31

Thanks very much for your help here, it was spot on!

What I hope to acheive here is to schedule this import periodically in order to keep the list in the SQL table up to date with AD, however what I have noticed is that after running more than once it does not 'sync' the info, it duplicates it. Again, I'm sure this is down to my ignorance on how SQL operates and is probably the expected result, but could you suggest a way of modifiying the script for it to check if the result already exists before adding it as a new row?

Thanks in advance

Adam

_____________________

Adam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-21 : 08:28:42

look in Books Online for MERGE.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources