Solved

Login information

Posted on 2016-08-16
9
25 Views
Last Modified: 2016-08-19
Hello,
Query to find user name in sql query.
Need to update a field in table which will store the username who isconnected to sql server.

Cheers
0
Comment
Question by:RIAS
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41758215
SELECT SUSER_NAME()

Open in new window

will return the name of the logged on user in SSMS.  But I don't understand the purpose of this?  Are you doing this through code, and if so can't you pass the environment variable for username through that way?
1
 
LVL 20

Expert Comment

by:Russ Suter
ID: 41758217
SELECT SYSTEM_USER will tell you the login in use.
SELECT CURRENT_USER will tell you the user.

These aren't necessarily the same thing. It depends on the user-login mapping for the particular database.
0
 
LVL 25

Expert Comment

by:SStory
ID: 41758219
SQL Server is a multi user RDBMS so in many situations, many users could be connected. However if it is known to be a single user situation where it is just an app with SQL Server these might help:

2008 and forward:
SELECT CURRENT_USER;      this will return the current user

2005
SELECT SYSTEM_USER;         gets users currently executing the script

You can use  normal TSQL
UPDATE sometablename SET somefield=whatever
WHERE somecondition is true

with that information to do what you want I guess

Without a more clear explanation, help can't be further provided.
1
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!

 

Author Comment

by:RIAS
ID: 41758245
Just want an audit trail
0
 
LVL 12

Expert Comment

by:Dustin Saunders
ID: 41758275
What are you auditing?  Users in SSMS?  Are you intending for this to be a trigger or something?
0
 
LVL 25

Accepted Solution

by:
SStory earned 500 total points
ID: 41758313
0
 
LVL 7

Expert Comment

by:Senior IT System Engineer
ID: 41758701
What about

exec sp_who2

Open in new window


would that be helpful ?
0
 

Author Comment

by:RIAS
ID: 41758975
CREATE TABLE [dbo].[Audit_DEBUG](
      [SQL] [nvarchar](2000) NULL,
      [EntryDateTime] [datetime] NULL CONSTRAINT [EntryDateTime]  DEFAULT (getdate()),
      [User] [nvarchar](2000) NULL   CONSTRAINT   DEFAULT ((SYSTEM_USER)),
) ON [PRIMARY]

Any suggestions on this ?
1
 
LVL 25

Expert Comment

by:SStory
ID: 41762766
Are you getting an error? You do have an extra comma that I think shouldn't be there after SYSTEM_USER))
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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 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