September 22, 2010 at 6:01 am
Hey guys,
I'm using AD Group security, and it works fine, but i want to know which AD Group the user is coming in via (hope that makes sense).
I've searched and searched, but sadly I do not think this is possible (or at least remotely easy!).
Someone tell me im wrong and i'm just missing some basic sys variable or something of the sort?!
thanks,
jeff
September 22, 2010 at 7:28 am
There's no way I know of to do this in T-SQL as least not easily. I think it can be done using PowerShell. You should check out the following blogs: Chad Miller[/url], Aaron Nelson[/url], Ron Dameron
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2010 at 7:47 am
Thanks for the reply jack,
we've also found this: http://msdn.microsoft.com/en-us/library/ms190369.aspx
EXEC xp_logininfo 'ADGroup', 'members'
This shows all members of the AD group in question. We can build something around this.
However, this is apart of a bigger thing I'm working on... i want to use a Classifier Function with the resource governor to assign users to pools, but with AD Group security, i'm not sure how to capture the AD Group they are apart of.
but now, I can't execute this proc within the classifier function, so i need a work around for that as well 🙂
good times, good times!!
(btw: i realize this is the 2005 forums and no governor, but i wanted to find the group first, and work on the governor part next)
thx again
December 9, 2011 at 8:48 am
Jeff Kelly-310227 (9/22/2010)
Thanks for the reply jack,we've also found this: http://msdn.microsoft.com/en-us/library/ms190369.aspx
EXEC xp_logininfo 'ADGroup', 'members'
This shows all members of the AD group in question. We can build something around this.
However, this is apart of a bigger thing I'm working on... i want to use a Classifier Function with the resource governor to assign users to pools, but with AD Group security, i'm not sure how to capture the AD Group they are apart of.
but now, I can't execute this proc within the classifier function, so i need a work around for that as well 🙂
Jeff I've been faced the same problem. succeeded solved by IS_MEMBER('Domain\Group').
Hope it helps! 🙂
Cheers, Ondrej
part of function is here:
...
IF SUSER_SNAME() in ('DSDEV\M..L','DSDEV\P...Z','BossUser','e...e','BossUserTest','DS\AP_...SUPP','DS\X...X')
or is_member('DS\AP_...E')=1
SET @WorkLoad='DBOXwg'
else if SUSER_SNAME() in (
...
else
SET @WorkLoad='Unidentified';
December 9, 2011 at 8:53 am
Ondrej Liptak-260768 (12/9/2011)
Jeff I've been faced the same problem. succeeded solved by IS_MEMBER('Domain\Group').Hope it helps! 🙂
Cheers, Ondrej
part of function is here:
...
IF SUSER_SNAME() in ('DSDEV\M..L','DSDEV\P...Z','BossUser','e...e','BossUserTest','DS\AP_...SUPP','DS\X...X')
or is_member('DS\AP_...E')=1
SET @WorkLoad='DBOXwg'
else if SUSER_SNAME() in (
...
else
SET @WorkLoad='Unidentified';
Hmmm, obviously I didn't know about the IS_MEMBER() function, that seems like a potentially really useful function.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 9, 2011 at 8:59 am
Yap, try BOL for more detail! possible to use for roles of SQL box or NT login Groups of SQL server.
Fortunately no special privileges for calling user are needed.
Cheers, O.
December 9, 2011 at 10:29 am
The issue here with IS_MEMBER() is you have to try each group in turn. This is why I say ADUC (Active Directory Users and Computers or a command-line tool like dsquery is your friend. You can recursively get a list of the group memberships for the user in question and then compare them to the list in SQL Server. The reason I say that is because a user may be coming in and gaining security via membership in multiple Windows security groups. In this case the permissions aggregate.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply