Solved

Default a SQLServer 2005 field to the Network user creating the record

Posted on 2011-03-11
10
309 Views
Last Modified: 2012-08-13
I need to set the default value = to the network userid creating a record in a SQLServer table.  Currently I am using (suser_sname()), but this is the SQLUser ID,  I need the Windows Network userid.

Thanks in advance,

Brent
0
Comment
Question by:BFanguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 35110918
I believe that it gives you the username of the user who is connecting to sql server.  If your connection uses SQL Server authentication, then you will get that.

In other words, if you want to get the Windows network userid, then you must connect to sql server using Windows Authentication, not SQL server authentication.
0
 

Author Comment

by:BFanguy
ID: 35111079
Is their any way to get the user name if we are using sqlserver authentication?
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35111095
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35111164
....using sqlserver authentication will prevent you from doing this by default.
You'll get "sa"

"Is their any way to get the user name if we are using sqlserver authentication? "

... in code, in an application you are developing, would be the easiest way I can think of.
Otherwise, you would be creating your own custom function...use the IP address...reference some external script/lookup... then pass the value back.

Is there any reason you can't use Windows authentication ?
0
 

Author Comment

by:BFanguy
ID: 35111405
SqlServer authentication is the way it's designed from a software package that we purchased.
0
 
LVL 25

Accepted Solution

by:
Ron Malmstead earned 500 total points
ID: 35129804
I think I may have a solution for you, but it will add a little "cost" in this transaction.
It involves getting the hostname, using HOST_NAME()...then running a vbscript(WMI) from xp_cmdshell in SQL server and returning the result.


First,  enable xp_cmdshell comand in SQL server.
http://www.mssqltips.com/tip.asp?tip=1020

Next, ...add the vbs script attached...save it as "C:\whologon.vbs" on the server.

 
On Error Resume Next

If WScript.Arguments.Count <> 1 Then
  WScript.Echo "Syntax Error. Correct syntax is:"
  WScript.Echo "cscript whologon <machine name>"
  WScript.Quit
End If


strComputer = WScript.Arguments(0)   ' use "." for local computer 
strUser = "YOURDOMAIN\administrator" ' comment this line for current user
strPassword = "YourAdminPassword" ' comment this line for current user

' CONSTANTS
'
wbemImpersonationLevelImpersonate = 3
wbemAuthenticationLevelPktPrivacy = 6

'=======================================================================
' MAIN
'=======================================================================

' Connect to machine
'
If Not strUser = "" Then

	' Connect using user and password
	'
	Set objLocator = CreateObject("WbemScripting.SWbemLocator")
	Set objWMI = objLocator.ConnectServer _
		(strComputer, "root\cimv2", strUser, strPassword)
	objWMI.Security_.ImpersonationLevel = wbemImpersonationLevelImpersonate
	objWMI.Security_.AuthenticationLevel = wbemAuthenticationLevelPktPrivacy
	
Else

	' Connect using current user
	'
	Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2") 

End If

  Set objWMI = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
  Set colItems = objWMI.ExecQuery("SELECT * FROM Win32_ComputerSystem")

  For Each objItem In colItems
    
WScript.Echo objItem.UserName
   
  Next

Open in new window


Finally... create a stored procedure, user function, or trigger... out of the following sql....


Declare @hostname sysname, @cmd sysname

SET @hostname = HOST_NAME()
SET @cmd= 'cscript c:\whologon.vbs ' + @hostname
DROP TABLE #tmp
CREATE TABLE #tmp([output] varchar(500))
INSERT INTO #tmp
exec xp_cmdshell @cmd
SELECT * FROM #tmp WHERE [output] IS NOT NULL
AND [output] NOT LIKE '%Microsoft (R) Windows Script Host%'
AND [output] NOT LIKE '%Copyright (C) Microsoft Corporation%'
DROP TABLE #tmp
0
 

Author Comment

by:BFanguy
ID: 35129955
thanks, will give this a try.
0
 

Author Closing Comment

by:BFanguy
ID: 35151232
thanks.
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35166562
BFanguy,

I'm curious, for the sake of others who come upon this question...  how did you chose to implement this ?

Trigger ? stored procedure ? function ? or other combination thereof?
0
 

Author Comment

by:BFanguy
ID: 35166801
combination
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question