need function to return username regardless of sysadmin role

Question

need function to return username regardless of sysadmin role

Aaron Olmstead

SSC Eights!

Points: 805
More actions
November 1, 2004 at 9:03 am

#84082

I have an application in which I often use the "CURRENT_USER" function. I have an Employees table in which one of the columns is "Username", and I frequently make references to this table for triggers, default values, and other FE functionality.
I am running into problems because there are users of the application that have the sysadmin role, in which case the CURRENT_USER function always returns "dbo" instead of the actual username that their login is mapped to. (same situation w/ USER, This is causing me some pretty major headaches.
Is there any way to override this so that the function returns the acual username that the user's login is mapped to regardless of them being in the sysadmin role? (Or another alternative that could solve my problem)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

What about SUSER_SNAME()?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

This might be of some use .. to return environmet varialbes

master..xp_cmdshell 'set'

from: http://www.sqlservercentral.com/scripts/contributions/649.asp

Returns:

ALLUSERSPROFILE=C:\Documents and Settings\All Users

CommonProgramFiles=C:\Program Files\Common Files

COMPUTERNAME=John0987

ComSpec=C:\Windows\system32\cmd.exe

INCLUDE=C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\include

JSERV=C:\oracle\ora92/Apache/Jserv/conf

LIB=C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Lib

NUMBER_OF_PROCESSORS=1

OS=Windows_NT

Path=C:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files\Common Files\Adaptec Shared\System;C:\MSSQL7\BINN;C:\Program Files\Microsoft SQL

Server\80\Tools\BINN;c:\Program Files\Microsoft SQL Server\90\Tools\Binn

PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH

PROCESSOR_ARCHITECTURE=x86

PROCESSOR_IDENTIFIER=x86 Family 6 Model 8 Stepping 0, AuthenticAMD

PROCESSOR_LEVEL=6

PROCESSOR_REVISION=0800

ProgramFiles=C:\Program Files

PROMPT=$P$G

SystemDrive=C:

SystemRoot=C:\Windows

TEMP=C:\Windows\TEMP

TMP=C:\Windows\TEMP

USERPROFILE=C:\Documents and Settings\LocalService

VSCOMNTOOLS="C:\Program Files\Microsoft Visual Studio .NET\Common7\Tools\"

windir=C:\Windows

WV_GATEWAY_CFG=C:\oracle\ora92\Apache\modplsql\cfg\wdbsvr.app

NULL

This would at least give you the computer name - and you might be able to work from there to what you need

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]