• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Function to query AD group

Does anyone have a function that i can use to determine if a specific user is a member of a specific ad group?

I'm going to pass a userid into a stored procedure and return one data set if a member of group 1 return another data set if a member of group 2.
0
gdspeare
Asked:
gdspeare
  • 2
1 Solution
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
SQL Server does not contain this information, but you can use is_member:
http://msdn.microsoft.com/en-us/library/ms186271.aspx

I e, SELECT is_member('domain\groupname')
Or:
If (SELECT is_member('domain\groupname')) = 1
BEGIN
  --T-SQL code to run when user is member of the domain group
END

//Marten
0
 
gdspeareAuthor Commented:
Thanks martenrune -

i was hoping that someone has done something cool....i"m actually handling this way....

is_member won't work by itself because often i'm not looking for the current user.
ALTER procedure [dbo].[usp_CheckDomainAdmin] 

  @vch_User		varchar(255)	= 'user',
  @int_Member	int				= 0	OUTPUT


AS

execute as login = @vch_User

set @int_Member = (SELECT is_member('utmsa\domain admins'))

declare @output int

exec usp_CheckDomainAdmin  
	@vch_User = 'utmsa\law777',
	@int_MEmber = @output output

print @output

Open in new window

0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
This looks like it's going to work, but ...

does this mean your happy with the provided solution, or shall I come up with an alternative?

//Marten
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now