[dba-SQLServer] Finding current login group

Billy Pang tuxedo_man at hotmail.com
Sun Jul 4 04:08:23 CDT 2004


not really working.. I am currently trying to resuscitate an old toshiba 
4005CDT laptop.  it has served me well in university 6 years ago.  now it 
won't boot up.  when it powers up, I see Toshiba welcome screen and then 
blank screen.  I won't let it go out on me.. not on my watch :)




>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 20:29:41 +1200
>
>Thanks - finally found it.  Will test it out tomorrow.  Much appreciated.
>
>Why working so late?  and on a Saturday!
>
>David
>
>At 4/07/2004, you wrote:
>>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
>>
>>_______________________________________________
>>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
>

_________________________________________________________________
STOP MORE SPAM with the MSN Premium 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




More information about the dba-SQLServer mailing list