SQL Server Performance Forum – Threads Archive

Active Directory and sql server

Hi all, i want to delete a user from active directory using sql server query , is it possible or not…. can anybody help me out….
Regards
rajesh.

If there’s a command-line statement that you can enter in a DOS box, then you can execute that same statement through EXEC master.dbo.xp_cmdshell – you’ll need proper permissions on the server level, but I figure you already knew that. Not sure that it would be wise, though – you should use the proper tools to do this. There’s also a bunch of stored procedures whose names start with sp_OA, that help you manipulate OLE objects.

Try this: Adriaan’s code + ‘dsrm ObjectDN’
where ObjectDN is the name of the user object to be deleted. You can get a list of the parameters for dsrm by typing dsrm/? in DOS Let me know if this works. I can’t seem to post with the xp command statement, I keep getting an error. Anybody know why? – Tahsin

quote:Originally posted by Adriaan If there’s a command-line statement that you can enter in a DOS box, then you can execute that same statement through EXEC master.dbo.xp_cmdshell – you’ll need proper permissions on the server level, but I figure you already knew that. Not sure that it would be wise, though – you should use the proper tools to do this. There’s also a bunch of stored procedures whose names start with sp_OA, that help you manipulate OLE objects.

Tahsin, it’s a bug in the forum software – you can’t post xp_cmdshell as a single word (I cheated here by inserting bold markers between _ and c).

>>I keep getting an error. Anybody know why? Read this
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11567 Madhivanan Failing to plan is Planning to fail

It looks like Brad has locked down the server for security reasons. Seems like almost all functions mentioned here:http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=24 cause this error. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

xp_readmail —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Sorry, that one was another test, that should have failed. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Sorry, that one was another test, that should have failed. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Not only that but also these xp_sendmail xp_servicecontrol xp_snmp_getstate xp_snmp_raisetrap Madhivanan Failing to plan is Planning to fail

They are all from the right column. Now try that with the left one. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Test xp_grantlogin
xp_logevent
xp_loginconfig
xp_logininfo
xp_makewebtask
xp_msver Madhivanan Failing to plan is Planning to fail

Those from the top of that column should fail. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

… to return to the original question – Tahsin, the dsrm executable will no be installed on all SQL Server machines. Perhaps it comes standard with Windows Server editions? Here’s some ADO programming against an LDAP directory, which you might be able to translate into calls to the sp_OA* procedures … the following was used against a Unix-based LDAP – Dim connLDAP As ADODB.Connection
Dim rsLDAP As ADODB.Recordset Dim strColumnNames As String
strFieldNames = "comma-separated list" Set connLDAP = New ADODB.Connection connLDAP.Provider = "ADSDSOObject"
Should correspond to a DLL installed on the machine where this code is running.
In my case it was a VBA type library (activeds.tlb). connLDAP.CursorLocation = adUseClient ‘For one-way traffic connLDAP.Open "ADs Provider", "uid=login,ou=trusted,ou=top level name, o=whatever", "password?" Set rsLDAP = connLDAP.Execute( _
"<LDAP://LDAP.mydomain.com/o=whatever/ou=whatever/ou=whatever>;" & _
"(&(ObjectClass=whatever)(uid=*)(!(uid=_))(some field to filter on=*));" & strFieldNames & ";subtree")

I believe the dsrm executable is native to the server where Active Directory is installed, whether it is Windows 2000 or 2003. I guess I was assuming that Rajesh wanted to query a SQL statement from a place where the command was accessible, but that statement does not necessarily have to be true. The simplest solution would be to run a SP off the server where dsrm is available. Otherwise, I guess vbscript is another possible way to try and remove a user from an LDAP directory service. Thanks guys for the input on why I couldn’t post the xp_ commands. I guess next time I will try to wrap it around with some HTML characters like Adriaan did.

]]>