?
Solved

Login information

Posted on 2016-08-16
9
Medium Priority
?
33 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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

752 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