Login information

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
RIASAsked:
Who is Participating?
 
SStoryConnect With a Mentor Commented:
0
 
Dustin SaundersDirector of OperationsCommented:
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
 
Russ SuterCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
SStoryCommented:
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
 
RIASAuthor Commented:
Just want an audit trail
0
 
Dustin SaundersDirector of OperationsCommented:
What are you auditing?  Users in SSMS?  Are you intending for this to be a trigger or something?
0
 
Senior IT System EngineerIT ProfessionalCommented:
What about

exec sp_who2

Open in new window


would that be helpful ?
0
 
RIASAuthor Commented:
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
 
SStoryCommented:
Are you getting an error? You do have an extra comma that I think shouldn't be there after SYSTEM_USER))
0
All Courses

From novice to tech pro — start learning today.