SQLServerCentral / Article Discussions / Discuss content posted by mobasha / Article Discussions by Author / Who did what? / Latest Posts

RE: Who did what?

SQLRNNR — Mon, 09 Jan 2012 09:05:24 GMT

These scripts are for for current activity.

RE: Who did what?

logicinside22 — Mon, 09 Jan 2012 07:48:41 GMT

HI SQL RNNR I was testing your script so just want to make sure little thing in result set session_id is the SPID right?is there possibility with this script can show up the date and time of user . like what time they ran any query?Thanks

RE: Who did what?

SQLRNNR — Fri, 06 Jan 2012 16:38:43 GMT

Here's another twist on the same script[code="sql"]USE MASTERGoSELECT cr.DatabaseName ,s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name ,s.nt_domain, s.nt_user_name, c.client_net_address, c.local_net_address ,cr.ObjName ,cr.QueryFROM sys.dm_exec_sessions as sINNER JOIN sys.dm_exec_connections as c ON c.session_id = s.session_idCROSS APPLY (SELECT db_name(dbid) AS DatabaseName,object_id(objectid) AS ObjName,ISNULL((SELECT text AS [processing-instruction(definition)] FROM sys.dm_exec_sql_text(c.most_recent_sql_handle) FOR XML PATH(''), TYPE ),'') as Query FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) crORDER BY c.session_id[/code]

RE: Who did what?

SQLRNNR — Fri, 06 Jan 2012 16:15:45 GMT

Nice script indeed.

RE: Who did what?

logicinside22 — Fri, 06 Jan 2012 15:32:51 GMT

nice script ...amazing result which i want to see it.thanks

RE: Who did what?

Chadwick-788357 — Fri, 19 Dec 2008 12:39:52 GMT

Very helpful script. Thanks. I changed it a bit to make the code cleaner. This produces the same result:USE MASTERSELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name ,s.nt_domain, s.nt_user_name, c.client_net_address, c.local_net_address --,c.connection_id,c.parent_connection_id, c.most_recent_sql_handle ,(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as sqlscript ,(SELECT db_name(dbid) FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as databasename ,(SELECT object_id(objectid) FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as objectnameFROM sys.dm_exec_sessions as sINNER JOIN sys.dm_exec_connections as c ON c.session_id = s.session_id

RE: Who did what?

keliu — Mon, 27 Oct 2008 08:26:27 GMT

I like to know when who is doing in the query.Can anybody help?Thanks.Ke

RE: Who did what?

CJensen-489142 — Mon, 28 Apr 2008 15:34:42 GMT

Looks like good info to have. . ., but my SSMS query keeps rejecting the code. It complains about the three inline queries for the elements from dm_exec_sql_text function, doesn't like the '.' within the function argument. Is this a database compatibility issue?Any ideas?:)

RE: Who did what?

TaoCanis — Mon, 28 Apr 2008 11:57:40 GMT

Hi,Is this script for 2005 only or does it work for 2000? Thanks.

RE: Who did what?

RBarryYoung — Tue, 25 Mar 2008 23:53:57 GMT

MobashA, you're welcome.

RE: Who did what?

Dugi — Tue, 25 Mar 2008 07:06:45 GMT

very nice script...thnx!

RE: Who did what?

mobasha — Tue, 25 Mar 2008 00:46:58 GMT

thanks all.ur solution were v.smart.thanks a lot for the help.

RE: Who did what?

RBarryYoung — Mon, 24 Mar 2008 17:39:27 GMT

I don't know any tricks to get periodic query refreshing inside SSMS, I always write a VB.net client to do this kind of stuff.

RE: Who did what?

RBarryYoung — Mon, 24 Mar 2008 17:34:01 GMT

[quote][b]mobasha (3/11/2008)[/b][hr]thanks.do any one have a solution if i need to run this select every 2 seconds?i have been trying but am stuck in a dead end.[/quote]Well, this does what you asked for, but probably is not what you want:[code]While (1=1) BEGIN Waitfor Delay '00:00:02' {your query's} END[/code]

RE: Who did what?

RBarryYoung — Mon, 24 Mar 2008 17:29:23 GMT

Probably should be listed under "Scripts" instead of "Articles".

RE: Who did what?

timothyawiseman — Mon, 24 Mar 2008 17:18:08 GMT

After playing with it a little this is definitely a useful query, but the article could certainly go with more explanation about what its intent is and how it functions. That would make it a bit easier to understand.

RE: Who did what?

mobasha — Tue, 11 Mar 2008 00:48:38 GMT

thanks.do any one have a solution if i need to run this select every 2 seconds?i have been trying but am stuck in a dead end.

RE: Who did what?

RBarryYoung — Mon, 10 Mar 2008 11:39:35 GMT

Nice. I will keep this one.

Who did what?

mobasha — Sun, 09 Mar 2008 23:38:18 GMT

Comments posted to this topic are about the item [B]Who did what?[/B]