Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Login information

Posted on 2016-08-16
9
Medium Priority
?
34 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
[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
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 13

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

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

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 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