• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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

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
BFanguy
Asked:
BFanguy
  • 5
  • 4
1 Solution
 
tlovieCommented:
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
 
BFanguyAuthor Commented:
Is their any way to get the user name if we are using sqlserver authentication?
0
 
Ron MalmsteadInformation Services ManagerCommented:
0
Technology Partners: 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!

 
Ron MalmsteadInformation Services ManagerCommented:
....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
 
BFanguyAuthor Commented:
SqlServer authentication is the way it's designed from a software package that we purchased.
0
 
Ron MalmsteadInformation Services ManagerCommented:
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
 
BFanguyAuthor Commented:
thanks, will give this a try.
0
 
BFanguyAuthor Commented:
thanks.
0
 
Ron MalmsteadInformation Services ManagerCommented:
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
 
BFanguyAuthor Commented:
combination
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now