[dba-SQLServer] Finding current login group

Ken,

The problem is that while egas\David is the windows login for the user, the 
SQL login is egas\BillingUsers.  That is, the user accessed SQL because he 
is a member of egas\BillingUsers.  egas\David is not a login in 
SQL.  Because egas\David is not in sysusers there is no match with 
Current_User (which returns the windows login, not the SQL login).

David

At 2/07/2004, you wrote:
>David,
>
>Yes, but that is only the criteria for the query.  By joining sysusers to
>sysmembers, once to represent users and a second time to represent roles,
>you can determine which roles a user belongs to, which may be one or many, I
>don't know how you managed that.
>
>Ken
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
>Emerson
>Sent: Friday, July 02, 2004 2:48 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Finding current login group
>
>Ken,
>
>Current_user gives me the name of the user (egas\David), not the group that
>that they were logged in as (egas\BillingUsers).
>
>David
>
>At 1/07/2004, you wrote:
> >Could you use something like this?
> >
> >select sg.name
> >from sysusers su inner join sysmembers sm on su.uid = sm.memberuid
> >         inner join sysusers sg on sm.groupuid = sg.uid
> >where su.name = current_user
> >
> >When I run this, it returns all the groups that a particular user is a
> >member of.
> >
> >Ken
> >
> >-----Original Message-----
> >From: dba-sqlserver-bounces at databaseadvisors.com
> >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
>H
> >Tapia
> >Sent: Thursday, July 01, 2004 4:15 PM
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] Finding current login group
> >
> >sp_who returns domain and userid not the usergroup, and in fact I don't
> >know how to get he usergroup name within a sproc, but one workaround is
> >to create seperate functions and assign them rights based on the NT
> >groups, you can have the sprocs return some type of value based on their
> >usergroup name and this will provide you with the info you are seeking.
> >
> >
> >Jeff Barrows wrote On 7/1/2004 3:44 PM:
> >
> > >Have you tried:      EXECUTE sp_who       in the SQL Query Analyser?
> > >
> > >       -----Original Message-----
> > >       From: David Emerson [mailto:davide at dalyn.co.nz]
> > >       Sent: Thu 7/1/2004 5:11 PM
> > >       To: dba-SQLServer at databaseadvisors.com
> > >       Cc:
> > >       Subject: [dba-SQLServer] Finding current login group
> > >
> > >
> > >
> > >       SQL2000
> > >
> > >       I am using Windows Authentication.  I have set up Windows groups
> >that users
> > >       belong to.  These groups are set up in SQL as logins (this means
> >that new
> > >       users just need to be added to the windows group to be given
>access
> >to all
> > >       the databases with permissions for the group).
> > >
> > >       Within my ADP I need to find out what login the user has accessed
> >the SQL
> > >       database from so that I can show/hide controls etc.  However, when
>I
> >use
> > >       the CURRENT_USER variable in a Sproc it returns the user, not the
> >group
> > >       they have come in by.
> > >
> > >       EG - egas\David is a member of egas\BillingUsers Group.
> >egas\BillingUsers
> > >       is set up as a login in SQL (egas\David is not).  When egas\David
>is
> >using
> > >       a database he has accessed it because he is a member of
> > >       egas\BillingUsers.  Is there a way to find out the login that was
> >used for
> > >       egas\David to get in (CURRENT_USER returns egas\David, I want
> > >       egas\BillingUsers)?
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com