How to Find which User are in Group ID of SQL Server

Hi Team, I have one Group ID like Comfin\SQL_ADMIN. I want to know which all user are in this Group ID.

Thanks Basit

more ▼

asked Apr 06, 2011 at 08:31 AM in Default

avatar image

basit 1
509 57 65 91

Is this a Windows Domain group, Windows Local group, or Database Role? If you have a database role within SQL Server I simply use: exec sp_helprolemember 'RoleName'

Apr 06, 2011 at 01:36 PM Shawn_Melton

It is windows group and want to know who are the member of this group

Apr 07, 2011 at 03:11 AM basit 1

If you are wanting to do this through SQL Server check Pavel's answer.

If you happen to have Powershell in your environment you can utilize this to check a group membership. Which I have not messed with much but I believe something like this should work: get-adgroupmember -Identity ComfinSQL_ADMIN You can check this link out: http://technet.microsoft.com/en-us/library/ee617195.aspx

Apr 07, 2011 at 06:51 AM Shawn_Melton
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If it is AD or Local group, then this info is not accessible from within SQL Server instance. Alhough there are some methos which enables querying AD.

You could create a linked server using OLE DB Provider for Microsoft Directory Services. You can use this provider also in OPENROWSET command etc. It is possible to write CLR method which will handle this.

In case you were speaking about role, then SQL Server reole members you can easily list as follow:

     dpr.name AS RoleName
     ,dprm.name AS RoleMemberName
 FROM Sys.database_role_members drm
 LEFT JOIN sys.database_principals dpr on drm.role_principal_id = dpr.principal_id
 LEFT JOIN sys.database_principals dprm on drm.member_principal_id = dprm.principal_id


You can check this microsoft KB Article Performing a SQL distributed query by using ADSI which describes how to query LDAP from SQL Server. There are links to many sources related to querying ActiveDirectory and LDAP. Don't know if they mention this somewhere in the article, but you have enabled Ad Hoc Distributed Queries on the server to use ADSI.

 sp_configure 'show advanced options', 1
  reconfigure with override
 sp_configure 'Ad Hoc Distributed Queries', 1 

more ▼

answered Apr 06, 2011 at 08:43 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

If this is a Windows Group and you don't have control over the group or access to Active Directory Users and Computers then ask a member of your security team who manages AD to provide you with a list. If you have access to query this yourself @Pavel Pawlowski has given you a top notch way of getting the data yourself if this is something you need on a regular basis. Otherwise you could install the Admin Pack for windows on your workstation and access AD through start, programs, Administrative Tools, Active Directory Users and Computers and search AD that way. Your options are numerous for this.

more ▼

answered Apr 07, 2011 at 07:28 AM

avatar image

40.9k 39 94 168

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 06, 2011 at 08:31 AM

Seen: 17324 times

Last Updated: Apr 06, 2011 at 08:31 AM

Copyright 2018 Redgate Software. Privacy Policy