Question :
I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical.
I am trying to query Active Directory services, on 2 of them this is working correctly, but one of them is throwing an error
Cannot get the column information from OLE DB provider “ADsDSOObject” for linked server “ADSI_TEST”.
If I don’t use Active Directory Server Name in the query it works fine.
Do you have any idea?
Answer :
to query the active directory I use the procedure xp_logininfo
Requires membership in the sysadmin fixed server role or membership in
the public fixed database role in the master database with EXECUTE
permission granted.
I have been using the following script and it works fine for me:
SELECT @@SERVERNAME
--myserver
DECLARE @NTLogin nvarchar(128);
SET @NTLogin = 'mycompanyperson_or_group_I_want_to_check'
DECLARE @UserList TABLE (
[Account Name] nvarchar(128),
[Type] nvarchar(128),
[Privilege] nvarchar(128),
[Mapped Login Name] nvarchar(128),
[Permission Path] nvarchar(128) )
INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'all' --insert group information
IF EXISTS (SELECT * FROM @UserList WHERE [Type] = 'group') --only if it's a group
INSERT INTO @UserList EXEC xp_logininfo @NTLogin, 'members' --insert member information
SELECT * FROM @UserList