[dba-SQLServer] Finding current login group

David Emerson davide at dalyn.co.nz
Fri Jul 2 21:34:45 CDT 2004


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




More information about the dba-SQLServer mailing list