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

  • if a quey returns a value then where is the security

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

    @merlin you can use SP_WHO
    GO

    Reply
  • how to use login information to execute a sql thru task scheduler

    Reply
  • I am executing below query in sql server 2008 from code(C#) ,

    SqlCommand dbCmd = new SqlCommand(“Execute as User=’newUser'”, connection);
    connection.Open();
    dbCmd.ExecuteNonQuery();
    connection.Close();

    it works fine.

    and then I am creating temporary(starting with #) table,

    CREATE TABLE #MyTable (Id INT, Name varchar(50))

    but it throws exception “A severe error occurred on the current command. The results, if any, should be discarded.”

    Here SqlConnection is in open state.

    I have created “newUser” having reader, writer and reports permissions.

    What should i do to run all queries under “newUser” from C# code?

    Reply
  • Can anyone help me in creating a script to automatically map a login to a new database and make it a member of the db_owner

    Reply
  • What is the difference between sp_who and SELECT SYSTEM_USER

    Reply
  • can anyone help me please to show users name in side of my chatbox who are logging at that particular moment

    Reply
  • I can do this in the sql server studio but cannto get the current user in the view

    Reply
  • I have an sql 2K5 installation, there 2 vpn connections through dedicated lines connected to the sql, how can i see which user runs which query so i can kill the query if necessary? Thanx.

    Reply
  • Ever-Victoria
    May 14, 2015 8:44 am

    Hi
    How do i write a code to display logged in users information in a report using apex?

    Please help.

    Reply
  • thx Pinal!.

    Reply

Leave a Reply

Menu