Billy Pang
tuxedo_man at hotmail.com
Sat Jul 3 04:31:35 CDT 2004
"permission path" column of xp_logininfo? see below.. /* -- cut here */ DECLARE @sys_usr varchar(100) SET @sys_usr = SYSTEM_USER SELECT @sys_usr EXEC xp_logininfo @sys_usr /* -- cut here */ HTH Billy >From: David Emerson <davide at dalyn.co.nz> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Finding current login group >Date: Sat, 03 Jul 2004 14:34:45 +1200 > >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 > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ MSN Premium includes powerful parental controls and get 2 months FREE* http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines