obtaining list of NT Groups for given NT user name

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » obtaining list of NT Groups for given NT user...

obtaining list of NT Groups for given NT user name

Rate Topic

Display Mode

Topic Options

Author

Message

SQLZ

Posted Wednesday, October 03, 2001 5:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:36 PM
Points: 872, Visits: 834

Hi,

I'm trying to obtain a list of NT Groups that a given NT Username belongs to.

The system function IS_MEMBER almost does what I want it to, however, it requires you to pass an NT Group as a parameter and only works on the current user.

Somehow though, the function IS_MEMBER is able to query data relating to NT Groups on the domain so there must be a way.

I am unable to find where system function definitions are kept in SQL Server(are they kept in a system table somewhere?).

If anyone could tell me where I could get hold of system function definitions or how I can get hold of NT Group information that would be very much appreciated.

Thanks,

Karl Grambow

Karl
source control for SQL Server

Post #1200

Andy Warren

Posted Wednesday, October 03, 2001 7:02 AM

SSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:51 PM
Points: 6,461, Visits: 1,377

Now thats an interesting question! If you can't find a function in master, it's in a dll somewhere and inaccessible. Found this snippet, which is useful but not quite close enough:

-----------------------------------------------------------------------------
http://groups.google.com/groups?q=NetGroupGetMembers&hl=en&rnum=2&selm=umSb0JBlAHA.1968%40tkmsftngp04

--Code was posted by Fernando Guerrero

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

exec sp_configure 'allow updates', 1
reconfigure with override
go

exec sp_ms_upd_sysobj_category 1
go

checkpoint
go

CREATE PROC xp_GetNTGroupMembers
@acctname sysname --IN: NT group name
as
select Name
from OpenRowset(NetGroupGetMembers, @acctname) AS NT

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------------

Found that by thinking about where SQL would be using functionality like you discussed, first place I looked was xp_loginfo which uses the NetGroupGetMembers thingy.

Only other thing I've seen so far is get_sid, referenced in sp_validatelogins, but that won't do it either.

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22044

SQLZ

Posted Wednesday, October 03, 2001 8:09 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:36 PM
Points: 872, Visits: 834

Andy,

thanks for the reply. Regarding the NetGroupGetMembers thingy, I managed to find another one that almost gives me what I want. It's called NetUserGetGroups and is used in the same way.

select *
from OpenRowset(NetUserGetGroups, '<domain\user>') AS NT.

Unfortunately, it only seems to return the groups I'm a member of in the current domain.

For example, my user account is europe\karl.

Running the above query only returns those groups I'm a member of in the europe domain. I am however a member of some groups in the usa domain.

Do you have any ideas on what this NetUserGetGroups thingy is?

Perhaps if I can get into that I might be able to see where that information is coming from. I've never seen this type of call using OpenRowSet. Normally I'd expect to see a connection string along with all the other details.

Regards,

Karl Grambow

Karl
source control for SQL Server

Post #22045

Andy Warren

Posted Wednesday, October 03, 2001 9:16 AM

SSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:51 PM
Points: 6,461, Visits: 1,377

Looks like they are both api calls, this just gives you an interface to them.

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22046

PBirch

Posted Wednesday, October 03, 2001 5:30 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 24, 2008 11:15 AM
Points: 575, Visits: 35

I tried to solve this problem a year and a half ago. I tried many stored procedures (most undocumented) and I found nothing. The client decided after discussing the many hours it would take to create this functionality that Enterprise Manager was good enough.

Patrick Birch

Quand on parle du loup, on en voit la queue

Post #22047

Andy Warren

Posted Wednesday, October 03, 2001 8:29 PM

SSCertifiable

Group: Moderators
Last Login: 2 days ago @ 6:51 PM
Points: 6,461, Visits: 1,377

I can see where it would be possible though not fun to solve this by querying ActiveDirectory. If Enterprise Manager can do it, should be a way for us to - I'll try to work on it this weekend, maybe luck out!

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #22048

neiljacobson

Posted Sunday, October 07, 2001 9:49 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 5:01 PM
Points: 69, Visits: 21

Try:

EXEC master.dbo.xp_enumgroups
-- To see groups and descriptions (Local I think)

and:

Exec master.dbo.xp_ntsec_enumdomains
-- To see domains

Also check out the 'net user' comand line function.

Post #22049

« Prev Topic | Next Topic »

Permissions