SQL SERVER – 2005 – Get Current User – Get Logged In User

Interesting enough Jr. DBA asked me how he can get current user for any particular query is ran. He said he wants it for debugging purpose as well for security purpose. I totally understand the need of this request. Knowing the current user can be extremely helpful in terms of security.

To get current user run following script in Query Editor

SELECT SYSTEM_USER

SYSTEM_USER will return current user. From Book On-Line – SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER returns the name of the impersonated context.

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL

, , ,
Previous Post
SQL SERVER – Deterministic Functions and Nondeterministic Functions
Next Post
SQL SERVER – 2008 – Server Consolidation WhitePaper Download

Related Posts

67 Comments. Leave new

  • CREATE FUNCTION getNTUser (@spid int)
    RETURNS varchar(50) AS
    BEGIN
    DECLARE @ntUser varchar(50)
    select @ntUser = nt_username from master..sysprocesses where spid = @spid
    return @ntUser
    END

    declare @UserName nvarchar(16)

    select @UserName = dbo.GetNTUser(@@SPID)

    Reply
  • Harikrishna bura
    September 7, 2010 11:33 am

    How to know the UserID,Password of unknown server like in my office different servers are using i want know the login username and password.

    Reply
  • Thank you pinaldave,

    very helpful!

    Reply
  • how to get the sqlserver user name and password …

    Reply
  • to Jim,

    Incorrect syntax near the keyword ‘declare’.
    ?

    Reply
  • How can i get the ipaddress from script

    Reply
  • neelesh kumar
    May 11, 2011 4:29 pm

    hi pinal,

    provide me the query for find out all database login with user detail (when with which user has log in and when out) detail within particular server.

    Reply
  • Hi Dave,

    I want a query to find out the password of sa for the current instance.

    Reply
  • Hi pinal,
    is it possible to track all activities (edit,insert,delete,enabling/disabling of triggers,editing of SP,etc) and create a log on SQL Server?
    Activities can be from any instance and can we track the machine name too of the client machine??

    Reply
    • Dheeresh Verma
      June 25, 2012 5:40 pm

      hiiiii JitinJ,,, you can use DDL Triggers…you need to create trigger on Database instead of Tables. like this.

      CREATE TRIGGER DDLTrigger_Sample
      ON DATABASE
      FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
      AS
      BEGIN
      END

      in this Script you ned to use EventData(). it returns the data into the XML format this function will help you to keep log of all activity with your DB.

      for your help..approach here

      Reply
  • I have work on Oracle and Mysql can you please let me any link that can make me understand complete sqlserver

    Reply
  • vinay (@vinayprasadv)
    November 4, 2011 5:46 pm

    is there a way to fine.. how many logins are there and to which databases they have access and what permissions?

    Reply
  • hello …how do i find username and password for sql authentication mode.
    and how do i operate ms sql from ms dos.

    Reply
    • I’m pretty sure it’s not possible to return passwords for SQL Auth logins. Use SQLCMD to operate SQL from MS DOS. See books online for more info.

      Reply
  • I am trying to get current user id with a select statement in sql server 2008. Is it possible

    Reply
  • Hi,

    I’m trying to write a sql query to graph data on Joomla’s plotalot extension. There are two users (user ID 1, and user ID 2). Bother users log into the front page and enter a score for that day (Nov 1, score: 3. Nov 2, score 9, Nov 3: score 4, etc….) Now I want a user to be able to login (let’s say user ID 1), and see only his scores. To built the plotalot chart, I use the query: SELECT UNIX_TIMESTAMP(input_datetime_3), input_select_2 FROM jos_chronoforms_data_datascores2
    WHERE cf_user_id = 1.

    But how do I write a query that will identify whoever is logged in? I don’t want to say: “WHERE cf_user_id = 1” Instead, I want to say: “WHERE cf_user_id = (currently logged in user).

    Any help is greatly appreciated!

    Reply
  • Hi… can i know what is the parameter for who is currently login to the computer in order to run the query… i need to know who is currently login to a computer… not to sql server…

    Reply
    • You could get this information from the USERNAME environment variable. If you open up a CMD prompt and type ‘SET’ you will see this populated with the currently logged in user.

      Reply
  • I need to build an sql of all users who have logged the last three months. I’ve trying to understand system sp_who, but I don understand how to set start date of results.

    Reply
  • I need to find out the Windows User Id for users who connect to the SQL Server using the same SQL User Id. Is it possible?

    Reply
  • niyaz ahamed
    March 6, 2012 9:51 pm

    I want to find sqlserver authentication username and password how to find in sqlserver 2008 R2.

    Reply
  • Hi,
    Is there a way to get at the current user in a View with Indexes . I want the current_user in my where clause.
    Thank you!

    Reply
  • HI

    i want to check No. of user logged in now. SELECT SYSTEM_USER its show only sa.

    Reply

Leave a Reply

Menu