Retive username / machine name
in [Programming SQL]
|
From: Rahul on 17 Jul 2008 01:28 Hi, How can i retirve information as follows : 1. User name who execute an sql statement. 2. machine name (on which machine sql statement execute). 3. is sql statement execute by directly by management studio/ qa or an application. and if an appliction, can i get the application name? Rahul
From: Uri Dimant on 17 Jul 2008 02:56 SQL Server 2005 SELECT session_id, user_id, text FROM sys.dm_exec_requests AS R CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST WHERE plan_handle IS NOT NULL SELECT host_name, program_name, session_id, login_name, last_request_end_time, cpu_time FROM sys.dm_exec_sessions WHERE session_id >= 51 and session_id =@session from the above statement GO "Rahul" <verma.career(a)gmail.com> wrote in message news:a4bce7fd-76bc-4ccd-98ec-0deddeed8cf6(a)25g2000hsx.googlegroups.com... > Hi, > How can i retirve information as follows : > 1. User name who execute an sql statement. > 2. machine name (on which machine sql statement execute). > 3. is sql statement execute by directly by management studio/ qa or an > application. and if an appliction, can i get the application name? > > Rahul
From: Rahul on 17 Jul 2008 04:42 On Jul 17, 11:56 am, "Uri Dimant" <u...(a)iscar.co.il> wrote: > SQL Server 2005 > SELECT session_id, user_id, text > > FROM sys.dm_exec_requests AS R > > CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST > > WHERE plan_handle IS NOT NULL > > SELECT host_name, > > program_name, > > session_id, > > login_name, > > last_request_end_time, > > cpu_time > > FROM > > sys.dm_exec_sessions > > WHERE > > session_id >= 51 and session_id =@session from the above statement > > GO > > "Rahul" <verma.car...(a)gmail.com> wrote in message > > news:a4bce7fd-76bc-4ccd-98ec-0deddeed8cf6(a)25g2000hsx.googlegroups.com... > > > > > Hi, > > How can i retirve information as follows : > > 1. User name who execute an sql statement. > > 2. machine name (on which machine sql statement execute). > > 3. is sql statement execute by directly by management studio/ qa or an > > application. and if an appliction, can i get the application name? > > > Rahul- Hide quoted text - > > - Show quoted text - Uri, Thanks for reply. Right now i am using sql server 2000, And no i am creating a trigger, where i want to log which user or machine name has execute update, delete or insert statement, that's why i required to retrive machinename, user name or "statement execute by an application or sqa" Your solution retrive information by providing sessionid. Can you please provide me some more inputs. Rahul
From: Uri Dimant on 17 Jul 2008 05:14 Rahul It is limited (compares to SQL Server 2005) in SQL Server 2000 Take a look at HOST_NAME() ,SYSTEM_USER...... "Rahul" <verma.career(a)gmail.com> wrote in message news:4db3be00-7906-4903-b72f-050e7244e4d8(a)y38g2000hsy.googlegroups.com... On Jul 17, 11:56 am, "Uri Dimant" <u...(a)iscar.co.il> wrote: > SQL Server 2005 > SELECT session_id, user_id, text > > FROM sys.dm_exec_requests AS R > > CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST > > WHERE plan_handle IS NOT NULL > > SELECT host_name, > > program_name, > > session_id, > > login_name, > > last_request_end_time, > > cpu_time > > FROM > > sys.dm_exec_sessions > > WHERE > > session_id >= 51 and session_id =@session from the above statement > > GO > > "Rahul" <verma.car...(a)gmail.com> wrote in message > > news:a4bce7fd-76bc-4ccd-98ec-0deddeed8cf6(a)25g2000hsx.googlegroups.com... > > > > > Hi, > > How can i retirve information as follows : > > 1. User name who execute an sql statement. > > 2. machine name (on which machine sql statement execute). > > 3. is sql statement execute by directly by management studio/ qa or an > > application. and if an appliction, can i get the application name? > > > Rahul- Hide quoted text - > > - Show quoted text - Uri, Thanks for reply. Right now i am using sql server 2000, And no i am creating a trigger, where i want to log which user or machine name has execute update, delete or insert statement, that's why i required to retrive machinename, user name or "statement execute by an application or sqa" Your solution retrive information by providing sessionid. Can you please provide me some more inputs. Rahul
From: Razvan Socol on 17 Jul 2008 15:00
Uri Dimant wrote: > Take a look at HOST_NAME() ,SYSTEM_USER...... .... and APP_NAME(). You can also look in sysprocesses to find this info. -- Razvan Socol SQL Server MVP |