August 3, 2016 at 12:42 pm
Hello All,
I need to determine when a user last accessed a table in a SQL Server 2012 environment.
I have a few scripts that will tell me when a table was last scanned or accessed but not by user.
Is there a table(s) I can join that will provide me with those details? I would think it’s possible to do, I just don’t know which table or combination of tables to use.
I’m familiar with the sys.dm_db_index_usage_stats table which will give me some info, but that doesn’t include anything related to user.
The log files will just tell me when a user login succeeded but doesn’t provide enough info.
Can someone please guide me in the right path or suggest some tables to look at or a script that is already design for what I’m looking for? I'm not sure if it makes a difference on what type of activity the user is performing such as update, insert or select but I would need to know any such activities.
Please let me know if you have any questions.
Thanks in advance
Ronnie
August 3, 2016 at 12:48 pm
Ronnie Jones (8/3/2016)
Hello All,I need to determine when a user last accessed a table in a SQL Server 2012 environment.
I have a few scripts that will tell me when a table was last scanned or accessed but not by user.
Is there a table(s) I can join that will provide me with those details? I would think it’s possible to do, I just don’t know which table or combination of tables to use.
I’m familiar with the sys.dm_db_index_usage_stats table which will give me some info, but that doesn’t include anything related to user.
The log files will just tell me when a user login succeeded but doesn’t provide enough info.
Can someone please guide me in the right path or suggest some tables to look at or a script that is already design for what I’m looking for? I'm not sure if it makes a difference on what type of activity the user is performing such as update, insert or select but I would need to know any such activities.
Please let me know if you have any questions.
Thanks in advance
Ronnie
Unless you have some custom auditing or extended events in place you simply can't do that. You could get this going forward but if this is in the past it is too late.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 3, 2016 at 1:05 pm
Just shoot them an email and ask.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 3, 2016 at 1:42 pm
I did see an article on setting up auditing but thought there was another option.
Is auditing the best way to capture that or setting up Profiler the way to do it?
Ronnie
August 3, 2016 at 1:48 pm
I wish it was as easy as sending an email...
August 3, 2016 at 2:57 pm
Ronnie Jones (8/3/2016)
I wish it was as easy as sending an email...
If the issue is that the user (or domain group) should not access a table, then that should be approached as an autorization issue, granting or denying permissions.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 3, 2016 at 4:04 pm
It's not so much that they should not, I'm just trying to verify when. Their access is good.
I've setup auditing for the DB/Table I need info on so, I'll capture what I need going forward. I just thought there was a table that retained user access data.
August 4, 2016 at 8:33 am
Ronnie Jones (8/3/2016)
It's not so much that they should not, I'm just trying to verify when. Their access is good.I've setup auditing for the DB/Table I need info on so, I'll capture what I need going forward. I just thought there was a table that retained user access data.
There is a lightweight default trace that is enabled and running out of the box, and it audits things like DDL, security, and DBCC events, nut not routine user activity.
However, there will be a performance cost when you're capturing events like every time a user selects from a table and auditing all those events to a table. It could be a large load on a high volume transaction processing database, so you'll need to apply conservative filters.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2016 at 8:34 am
OK, so what is SQLServerCentral doing to URLs that link to SimpleTalk ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 4, 2016 at 3:27 pm
Thank you for that info. Not quite at the level of detail that I was looking for but VERY GOOD information none-the-less!!
I definitely didn't know about the default traces that are enabled during setup and I see many uses for that information...
Thanks again!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply