[dba-SQLServer] Finding current login group

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)?
>	
>  
>
-- 
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com