Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-11
10
Medium Priority
?
320 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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