How To Find Your Groups On Domain_9

Remove From My Forums

Observe out the Advertizement group members from SQL Server Case

Question
We have an AD group called "Test_Read" on windows server and besides created Advert group in sql server also. Then added 10 users on Advert groups. We need to detect out the user names existing on AD groups using whatever SP or query on SQL Server. Is at that place some we can see the on SQL Server for AD group members ?

We can see in Active Directory manually, only nosotros demand to encounter in SQL Server query/sp.

All replies

Hmmm, a windows login if he connects via local windows grouping will be seen every bit local group login and not as windows login...

Perchance writing some PowerShell script on Windows level volition assistance y'all out

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Hmmm, a windows login if he connects via local windows grouping volition be seen equally local group login and not as windows login...

Perhaps writing some PowerShell script on Windows level volition help y'all out

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
howdy,

Yous may use linked server using the ADsDSObject provider and query the grouping membership using OPENROWSET or OPENQUERY.

I hope it helps.

J.

There are x type of people. Those who sympathize binary and those who do non.
My Blog
Yous can utilise dsquery, dsadd (on Bone level) and xp_cmdshell on SQL Server level.

1) To enable dsquery, dsadd on Windows Server 2008 you need to install characteristic:

Remote Server Assistants Tools -> Advertising DS and AD LDS Tools -> Advertizing DS Tools -> Active Directory Domain Services (AD DS) and Active Directory Lightweight Directory Services (AD LDS) Tools

2) to enable xp_cmdshell you need to execute:

EXEC sp_configure 'show adv', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
EXEC sp_configure 'show adv', 0
RECONFIGURE WITH OVERRIDE

3) Create proxy business relationship for xp_cmdshell, that has rights to run dsquery, dsadd and has rights to get group members from AD.

EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\Account', 'countersign'

four) Grant permissions on xp_cmdshell to your application user, that volition become group members

Map login on user in master database

Grant Exec on xp_cmdshell

5) Use post-obit script to get CN of members of group

IF OBJECT_ID ('tempdb..#group_members') IS NOT Cipher
DROP TABLE #group_members
CREATE TABLE #group_members
( ID int IDENTITY (1,1),
MemberCN nvarchar (1024) )

DECLARE @cmd nvarchar (1024)
DECLARE @GroupName nvarchar (256)
SET @GroupName = 'Domain Admins'
SET @cmd = 'dsquery group -proper noun ' + @GroupName + ' | dsget grouping -members'
INSERT INTO #group_members
EXEC xp_cmdshell @cmd

SELECT MemberCN from #group_members

DROP Tabular array #group_members
- Proposed as answer past Monday, November 28, 2011 iv:41 AM
Non certain if y'all still need this but may be someone else can do good.

SQL Server provides an extended stored process, xp_logininfo, to become information about Windows users and groups.

Syntax:

xp_logininfo [ [ @acctname = ] 'account_name' ]
[ , [ @option = ] 'all' | 'members' ]
[ , [ @privilege = ] variable_name OUTPUT]

xp_logininfo enables united states of america to detect out what Windows users are members of a detail Windows group. For instance: EXEC master.dbo.xp_logininfo 'DomainName\GroupName', 'members'

xp_logininfo besides enables usa to find out what Windows groups a detail Windows user is a member of. For case: EXEC master.dbo.xp_logininfo 'DomainName\UserName'

@acctname must be full qualified. If your Windows user or Windows grouping is local rather than in Active Directory, but use WorkgroupName instead of DomainName.

If the Windows user or Windows group exists only does not have access to the SQL Server case, you volition get an empty result set returned. Information technology volition not error.
- Proposed equally answer past Sagheer A Thursday, December 22, 2011 10:03 PM
Hi, You lot can brand use of xp_logininfo for this. For your requirement to list all the members of Advertising grouping; utilize post-obit query:

xp_logininfo 'DomainName\AD_GroupName', 'members'

It will list the members in post-obit output:

AccountName Type privilege mappedLoginname permission path.

Notation: Running this sp requires membership in the sysadmin server office or membership in the public fixed database role in the principal database with EXECUTE permission granted.

Thanks Sunil Gure
- Edited past Sunil Gure Tuesday, December 27, 2011 3:12 PM typo
- Proposed as answer by Manjunath C Bhat Tuesday, February 26, 2013 nine:49 AM

How To Find Your Groups On Domain_9,

Source: https://social.msdn.microsoft.com/forums/sqlserver/en-US/f111268d-a092-4372-9dc7-84dc5193bc9b/find-out-the-ad-group-members-from-sql-server-instance

Posted by: franksconot1980.blogspot.com