[dba-SQLServer] Finding current login group

Thanks Francis,

This also returns egas\david which is the Windows user.  I am after 
egas\BillingManager which is the windows group that egas\david belongs to 
which is the login.

I am close.  Here is the latest -

Billy Pang provided me with the following:

DECLARE @sys_usr varchar(100)
SET @sys_usr = SYSTEM_USER
EXEC master..xp_logininfo @sys_usr

When this is run in Query Analyser I get the correct recordset with a 
column called Permission Path.  This has the data in it (in this case 
'egas\BillingManager').

I want to turn this into a sproc which returns the value of Permission 
Path.  What I have so far is -

ALTER PROCEDURE dbo.spGetCurrentUser

DECLARE @sys_usr varchar(100), @qs nvarchar(4000)
SET @sys_usr = SYSTEM_USER
EXEC master..xp_logininfo @sys_usr

When run the sproc from query analyzer I get the recordset.  Now, how do I 
change the sproc so that it returns the value of the permission path column?

David


At 6/07/2004, you wrote:
>David,
>
>I am catching-up after my holiday, so I probably missed something.
>You tried CURRENT_USER and didn't like that, so what happened when you
>tried SUSER_SNAME instead? I'm just working my way through the BOL
>"Functions That Return User Names and User IDs" article which I
>assumed was your starting reference. I can setup an account to mimic
>your conditions if necessary, but I was hoping you could just let us
>know what the results of this function with your actual account were.
>
>Francis R Harvey III
>WB 303, (301)294-3952
>harveyf1 at westat.com
>
> > -----Original Message-----
> > From: dba-sqlserver-bounces at databaseadvisors.com
> > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf
> > Of David Emerson
> > Sent: Thursday, July 01, 2004 6:12 PM
> > To: dba-SQLServer at databaseadvisors.com
> > 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)?
><snip>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com