Billy Pang
tuxedo_man at hotmail.com
Sun Jul 4 03:25:21 CDT 2004
xp_logininfo sproc is in master db... so try... /* -- cut here */ DECLARE @sys_usr varchar(100) SET @sys_usr = SYSTEM_USER SELECT @sys_usr EXEC master..xp_logininfo @sys_usr /* -- cut here */ be aware of the execute permissions of this sproc...from bol, it reads: Execute permissions for xp_logininfo default to members of the db_owner fixed database role in the master database and members of the sysadmin fixed server role, but can be granted to other users. 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: Sun, 04 Jul 2004 19:50:47 +1200 > >Tried running this in Query Analyzer. Received error - couldn't find >xp_logininfo. What am I missing? > >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 >> >> > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Add photos to your e-mail with MSN Premium. 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