[dba-SQLServer] Finding current login group

David Emerson davide at dalyn.co.nz
Sun Jul 4 03:29:41 CDT 2004


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
>




More information about the dba-SQLServer mailing list