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 (http://blog.SQLAuthority.com), BOL
Is there a SQL stored procedure I can run in SQL 2000 that will give me the username of the windows user currently logged in?
sp_who can solve ur problem
You can also try:
SELECT SUSER_SNAME()
I found that select current_user was returning ‘dbo’. As my company is using windows accounts to connect to a database server the above command returned the result I was after.
In an interview, i was asked what query can you write to see what version of windows is SQL sever running on??? i couldn’t find the answer using google.??
select @@version
“SELECT @@version” will show you what version of SQL and Windows you are running.
“SELECT @@version” will show you what version of SQL and Windows you are running.
Tnx a lot for your Post!!! I did use it in one of my Triggers.
…
declare @uName char(30);
declare @Message char(250);
SELECT @uName=SYSTEM_USER;
…
Hello
I am trying to connect php and SQL server together. No matter how hard I try I always get the following error in apache error log
undefined function mssql_connect().
I am not able to connect at all, and always get HTTP 500 error. I have completed all procedures successfully like updating ntwdblib.dll, copying php_mssql.dll and php_sqlsrv.dll in the correct folders.
I have also changed php.ini files to uncomment php_mssql.dll and php_sqlsrv.dll.
I have XP service pack2, WAMP version 2.0, SQL server 2005 9.00.3042.00(Intel X86), PHP 5.2.9-1 and Apache 2.2.11.
Any help be greatly apprecciated
how do i obtain the role or roles of the logged on user name
Cleopas, simply use
EXECUTE AS USER = ‘username’
and to return back to your security context, use
REVERT
I want to know that Is it possible to track a user activities logged into sql server 2005.
for example if i login using “sa” or else. then track the activity for sa user .
I Searched and searched and finally found the RIGHT answer…
use ORGINAL_LOGIN()
Returns the name of the login that connected to the instance of SQL Server. You can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context switches.
ORIGINAL_LOGIN() returns the login name of the original context, but what will return the original username. I have the case where I need to know the username, not the login. Or can I take the login information and get the username associated with it with the sys tables?
Hi
Can you tell me how can I get the password expiration information of a active directory windows user from sql server 2005
thank you!
“show user;” returns the current user name
In SQL?
On oracle…
i need to write a script to find all SQL authenticated current users in an instance…. please any ine help me…
I need to write a script to find only all SQL authenticated users (including both logged in and logged out ) who all are connecting to an instance in a server ….
Please any one help me…
You could create an alert which is activated when a user logs in to SQL Server. I believe this is the SQLServer:GeneralStatistics performance object, and the counter ‘User Connections’. You could get the alert to execute a stored proc to log the user. leave it to run for a period of time and then collect your results. I’m sure there will be an easier way though via a system table.
You could use SQL Profiler to monitor if it’s not a huge user base?
hi pinal
i need query for all user in a particular database and also number of login also.
Thanks
Neelesh
Thanks!
Great information, I’ll get it on my triggers.
Hi all,
I am trying to develop a script that capture the user id/username of the user who is using ms dynamics 9.0 gp when the user edit a record. Could u help please.
Thank u
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)
Use GO statement after END
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.
Thank you pinaldave,
very helpful!
how to get the sqlserver user name and password …
to Jim,
Incorrect syntax near the keyword ‘declare’.
?
I have replied there. Use GO statement after END
How can i get the ipaddress from script
Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/get-ip-address.aspx
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.
Hi Dave,
I want a query to find out the password of sa for the current instance.
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??
I have work on Oracle and Mysql can you please let me any link that can make me understand complete sqlserver
is there a way to fine.. how many logins are there and to which databases they have access and what permissions?
hello …how do i find username and password for sql authentication mode.
and how do i operate ms sql from ms dos.
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.
I am trying to get current user id with a select statement in sql server 2008. Is it possible
Try this code
select USER,USER_ID ()
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!
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…