David Emerson
davide at dalyn.co.nz
Sun Jul 4 03:00:06 CDT 2004
OK, I have found it referred to in BOL and found it in the Master database. I will need to go into the client's office to set the permissions to enable it to be run but I think we are getting somewhere. Thanks. David At 3/07/2004, you wrote: >"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 > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com