What Users are in What Security Groups

Yahoo Archive
1
Hey, thank you very much! That's a slick query. Very nice.

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of sheehanam21
Sent: Wednesday, February 04, 2009 4:01 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: What Users are in What Security Groups

Here's the same scrip in T-SQL
SELECT userfile.name, secgroup.secgroupdesc, secgroup.secgroupcode,
userfile.dcduserid
FROM UserFile
CROSS JOIN
SecGroup
WHERE (userfile.grouplist LIKE '%' + secgroup.secgroupcode + '~%') OR
(userfile.grouplist LIKE '%' + secgroup.secgroupcode)

--- In vantage@yahoogroups.com, "apt876sp1" <atucker@...> wrote:
>
> Vic
>
> Here is what U did using ODBC and Access
> -----------------------
> SELECT PUB_UserFile.Name, PUB_SecGroup.SecGroupDesc,
> PUB_SecGroup.SecGroupCode, PUB_UserFile.DcdUserID
> FROM PUB_UserFile, PUB_SecGroup
> WHERE (((PUB_UserFile.GroupList) Like "*" & [PUB_SecGroup]!
> [SecGroupCode] & "~*" Or (PUB_UserFile.GroupList) Like "*" &
> [PUB_SecGroup]![SecGroupCode]));
> --------------------
> Hope it helps
> Allen
>
> --- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@>
> wrote:
> >
> > I wanted what I thought was a simple query of "What Vantage users
> are
> > assigned to which Security Groups" or conversely, a "List of
Groups
> with
> > associated Users".
> >
> >
> >
> > I know the UserFile and SecGroup tables... but what table serves
as
> the
> > cross reference? Cannot seem to find it.
> >
> >
> >
> > Thank you in advance.
> >
> >
> >
> >
> >
> >
> >
> > Vic Drecchio
> > ERP Administrator
> >
> > TIMCO Aviation Services
> > Greensboro, NC
> > Email: vic.drecchio@
> > Office: 336.668.4410 x3091
> > Mobile: 704.530.3092
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>




------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
2
I wanted what I thought was a simple query of "What Vantage users are
assigned to which Security Groups" or conversely, a "List of Groups with
associated Users".



I know the UserFile and SecGroup tables... but what table serves as the
cross reference? Cannot seem to find it.



Thank you in advance.







Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@...
Office: 336.668.4410 x3091
Mobile: 704.530.3092





[Non-text portions of this message have been removed]
3
(In 6.10.543) I just use the UserFile. There is GroupList field in it that contains an array of the groups a user is in. Each entry seperated by a tilde (~) character. I linking entries in an array as multiple recoird links I am not sure is possible. I've always resorted to exporting to Access when I've had to deal with arrays'. The may be a way with multiple table aliases (such as for commissions) but in this case the max number of entries is unknown.
-Todd C.


________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Vic Drecchio
Sent: Wednesday, January 21, 2009 10:12 AM
To: vantage@yahoogroups.com
Subject: [Vantage] What Users are in What Security Groups


I wanted what I thought was a simple query of "What Vantage users are
assigned to which Security Groups" or conversely, a "List of Groups with
associated Users".

I know the UserFile and SecGroup tables... but what table serves as the
cross reference? Cannot seem to find it.

Thank you in advance.

Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@...<mailto:vic.drecchio%40timco.aero>
Office: 336.668.4410 x3091
Mobile: 704.530.3092

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
Vic

Here is what U did using ODBC and Access
-----------------------
SELECT PUB_UserFile.Name, PUB_SecGroup.SecGroupDesc,
PUB_SecGroup.SecGroupCode, PUB_UserFile.DcdUserID
FROM PUB_UserFile, PUB_SecGroup
WHERE (((PUB_UserFile.GroupList) Like "*" & [PUB_SecGroup]!
[SecGroupCode] & "~*" Or (PUB_UserFile.GroupList) Like "*" &
[PUB_SecGroup]![SecGroupCode]));
--------------------
Hope it helps
Allen

--- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@...>
wrote:
>
> I wanted what I thought was a simple query of "What Vantage users
are
> assigned to which Security Groups" or conversely, a "List of Groups
with
> associated Users".
>
>
>
> I know the UserFile and SecGroup tables... but what table serves as
the
> cross reference? Cannot seem to find it.
>
>
>
> Thank you in advance.
>
>
>
>
>
>
>
> Vic Drecchio
> ERP Administrator
>
> TIMCO Aviation Services
> Greensboro, NC
> Email: vic.drecchio@...
> Office: 336.668.4410 x3091
> Mobile: 704.530.3092
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
5
There's no intermediate table. UserFile.grouplist and
Userfile.complist are tilde-separated multi-valued list.



--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@... ~ 734-864-5618 ~
www.advancedphotonix.com



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Vic Drecchio
Sent: Wednesday, January 21, 2009 11:12 AM
To: vantage@yahoogroups.com
Subject: [Vantage] What Users are in What Security Groups



I wanted what I thought was a simple query of "What Vantage users are
assigned to which Security Groups" or conversely, a "List of Groups with
associated Users".

I know the UserFile and SecGroup tables... but what table serves as the
cross reference? Cannot seem to find it.

Thank you in advance.

Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@... <mailto:vic.drecchio%40timco.aero>
Office: 336.668.4410 x3091
Mobile: 704.530.3092

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
6
I guess it would adhere too much to the industry-standard relational
database design to have an intermediate/cross-ref table. ;-)

Stupid tildes.


Thanks guys. :-)

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Wednesday, January 21, 2009 11:43 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] What Users are in What Security Groups

There's no intermediate table. UserFile.grouplist and
Userfile.complist are tilde-separated multi-valued list.



--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@... ~ 734-864-5618 ~
www.advancedphotonix.com



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Vic Drecchio
Sent: Wednesday, January 21, 2009 11:12 AM
To: vantage@yahoogroups.com
Subject: [Vantage] What Users are in What Security Groups



I wanted what I thought was a simple query of "What Vantage users are
assigned to which Security Groups" or conversely, a "List of Groups with
associated Users".

I know the UserFile and SecGroup tables... but what table serves as the
cross reference? Cannot seem to find it.

Thank you in advance.

Vic Drecchio
ERP Administrator

TIMCO Aviation Services
Greensboro, NC
Email: vic.drecchio@... <mailto:vic.drecchio%40timco.aero>
Office: 336.668.4410 x3091
Mobile: 704.530.3092

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]


------------------------------------

Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must
have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder
and Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/linksYahoo! Groups Links
7
Here's the same scrip in T-SQL
SELECT userfile.name, secgroup.secgroupdesc, secgroup.secgroupcode,
userfile.dcduserid
FROM UserFile
CROSS JOIN
SecGroup
WHERE (userfile.grouplist LIKE '%' + secgroup.secgroupcode + '~%') OR
(userfile.grouplist LIKE '%' + secgroup.secgroupcode)

--- In vantage@yahoogroups.com, "apt876sp1" <atucker@...> wrote:
>
> Vic
>
> Here is what U did using ODBC and Access
> -----------------------
> SELECT PUB_UserFile.Name, PUB_SecGroup.SecGroupDesc,
> PUB_SecGroup.SecGroupCode, PUB_UserFile.DcdUserID
> FROM PUB_UserFile, PUB_SecGroup
> WHERE (((PUB_UserFile.GroupList) Like "*" & [PUB_SecGroup]!
> [SecGroupCode] & "~*" Or (PUB_UserFile.GroupList) Like "*" &
> [PUB_SecGroup]![SecGroupCode]));
> --------------------
> Hope it helps
> Allen
>
> --- In vantage@yahoogroups.com, "Vic Drecchio" <vic.drecchio@>
> wrote:
> >
> > I wanted what I thought was a simple query of "What Vantage users
> are
> > assigned to which Security Groups" or conversely, a "List of
Groups
> with
> > associated Users".
> >
> >
> >
> > I know the UserFile and SecGroup tables... but what table serves
as
> the
> > cross reference? Cannot seem to find it.
> >
> >
> >
> > Thank you in advance.
> >
> >
> >
> >
> >
> >
> >
> > Vic Drecchio
> > ERP Administrator
> >
> > TIMCO Aviation Services
> > Greensboro, NC
> > Email: vic.drecchio@
> > Office: 336.668.4410 x3091
> > Mobile: 704.530.3092
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>