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 >