Solved

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

Posted on 2011-03-11
10
293 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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