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
Solved

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

Posted on 2011-03-11
10
303 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

856 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