Who did what?

Question

Who did what?

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice script indeed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Here's another twist on the same script

USE MASTER

Go

SELECT 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.Query

FROM sys.dm_exec_sessions as s

INNER JOIN sys.dm_exec_connections as c

ON c.session_id = s.session_id

CROSS 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)) cr

ORDER BY c.session_id

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

These scripts are for for current activity.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

The problem I just had with it is that I do not have permissions to run this on production.

Eric M Russell SSC Guru Points: 125522 More actions · Answer 6

Iwas Bornready (5/11/2016)
The problem I just had with it is that I do not have permissions to run this on production.

You can try requesting the following from the DBA:

-- allow viewing of object DDL (ie: table schemas and stored procedure text)

GRANT VIEW ANY DEFINITION TO [YourDomain\YourLogin];

-- allow querying of DMVs and system tables:

GRANT VIEW SERVER STATE TO [YourDomain\YourLogin];

-- allow starting / stopping of sql profiler or extended event traces:

GRANT ALTER TRACE TO [YourDomain\YourLogin];

Also, Adam Mechanic has a more comprehensive process viewer, which is in the form of a stored procedure called [sp_whoisactive]. However, if you can't deploy to production, then you can retofit it as a script if needed.

http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho