October 3, 2001 at 5:20 am
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
October 3, 2001 at 7:02 am
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:
-----------------------------------------------------------------------------
--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
October 3, 2001 at 8:09 am
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
October 3, 2001 at 9:16 am
Looks like they are both api calls, this just gives you an interface to them.
Andy
October 3, 2001 at 5:30 pm
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
October 3, 2001 at 8:29 pm
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
October 7, 2001 at 9:49 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply