[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Login information

Posted on 2016-08-16
9
Medium Priority
?
37 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 14

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

 

Author Comment

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

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 2000 total points
ID: 41758313
0
 
LVL 8

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

873 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