April 3, 2017 at 3:54 pm
I have the following query built:
USE MyDataBase;
GO
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
GROUP BY ius.[database_id], ius.[object_id];
If possible, how do I add which user it was that made a change?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
April 3, 2017 at 4:18 pm
SQL doesn't provide that info by default. You would need your own code already in place to provide that info.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2017 at 7:17 am
ScottPletcher - Monday, April 3, 2017 4:18 PMSQL doesn't provide that info by default. You would need your own code already in place to provide that info.
Thanks for the reply, Scott.
Is there anything native I can use in SQL Server 2008+ that will allow me to gather this information or is it something 3rd-party?
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
April 4, 2017 at 7:29 am
Sean Perkins - Tuesday, April 4, 2017 7:17 AMScottPletcher - Monday, April 3, 2017 4:18 PMSQL doesn't provide that info by default. You would need your own code already in place to provide that info.Thanks for the reply, Scott.
Is there anything native I can use in SQL Server 2008+ that will allow me to gather this information or is it something 3rd-party?
If your users are all connected through an application that uses a single connection, you will have to come up with a way of tracing the user through the app.
However, if every user has a unique login to SQL, then you can add a trigger to track changes - Normally in an Audit Table
April 4, 2017 at 7:40 am
DesNorton - Tuesday, April 4, 2017 7:29 AMSean Perkins - Tuesday, April 4, 2017 7:17 AMScottPletcher - Monday, April 3, 2017 4:18 PMSQL doesn't provide that info by default. You would need your own code already in place to provide that info.Thanks for the reply, Scott.
Is there anything native I can use in SQL Server 2008+ that will allow me to gather this information or is it something 3rd-party?
If your users are all connected through an application that uses a single connection, you will have to come up with a way of tracing the user through the app.
However, if every user has a unique login to SQL, then you can add a trigger to track changes - Normally in an Audit Table
Or simpler than individual logins, windows authentication.
April 4, 2017 at 7:55 am
What process is involved in editing a row; is that a stored procedure call or ad-hoc updates?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 4, 2017 at 8:04 am
There are some native-to-SQL options. Third-party apps don't really gain you that much for this.
The most common and probably the easiest to implement are triggers. You would have to test if ORIGINAL_LOGIN(), or some other built-in function, gave you what you needed. If not, you'd likely have to adjust the app to pass the data to the trigger somehow, either in CONTEXT_INFO() or from data stored somewhere.
If you want to do this for a lot of tables, you'd almost certainly want to write code to dynamically generate a static trigger for those tables. You do not want to use dynamic code in the trigger itself, however, because it's far too much overhead. Triggers should be as efficient as reasonably possible.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 4, 2017 at 3:28 pm
Eric M Russell - Tuesday, April 4, 2017 7:55 AMWhat process is involved in editing a row; is that a stored procedure call or ad-hoc updates?
The type or how of the edit is of no concern, we just want to know what or who is performing a edit in every instance.
I can see after research and the answers I've received that this isn't as simple as I thought and is much bigger.
It looks like I need something 3rd-party or to build some crazy in-house solution.
Thanks for the replies all, I appreciate your time!
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
April 5, 2017 at 8:32 am
Sean Perkins - Tuesday, April 4, 2017 3:28 PMEric M Russell - Tuesday, April 4, 2017 7:55 AMWhat process is involved in editing a row; is that a stored procedure call or ad-hoc updates?The type or how of the edit is of no concern, we just want to know what or who is performing a edit in every instance.
I can see after research and the answers I've received that this isn't as simple as I thought and is much bigger.
It looks like I need something 3rd-party or to build some crazy in-house solution.
Thanks for the replies all, I appreciate your time!
Ideally, no user account has db_datawriter membership (much less db_dbowner membership) on the database, and all DML operations on done via stored procedures which can also contain your auditing.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 5, 2017 at 11:48 am
Well, I think I think I might have found what I'm looking for.
It turns out that I can run PowerShell code against the site collections and it'll give me what we need. I guess i went down the wrong rabbit hole 🙁
I do appreciate all of your time, everyone. However, I've learned a lot, so it wasn't all for nothing 🙂
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
April 5, 2017 at 12:11 pm
Sean Perkins - Wednesday, April 5, 2017 11:48 AMWell, I think I think I might have found what I'm looking for.It turns out that I can run PowerShell code against the site collections and it'll give me what we need. I guess i went down the wrong rabbit hole 🙁
I do appreciate all of your time, everyone. However, I've learned a lot, so it wasn't all for nothing 🙂
Are you referring to a web application "site collection"? If so, then how do you derive from that the user who last updated a SQL Server table; from some user activity log?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 5, 2017 at 2:51 pm
Eric M Russell - Wednesday, April 5, 2017 12:11 PMSean Perkins - Wednesday, April 5, 2017 11:48 AMWell, I think I think I might have found what I'm looking for.It turns out that I can run PowerShell code against the site collections and it'll give me what we need. I guess i went down the wrong rabbit hole 🙁
I do appreciate all of your time, everyone. However, I've learned a lot, so it wasn't all for nothing 🙂
Are you referring to a web application "site collection"? If so, then how do you derive from that the user who last updated a SQL Server table; from some user activity log?
After research and posting these questions, I realized I just needed to find who edited or updated things on the sites. Initially, I figured all that data would be housed in the tables somewhere, apparently that isn't the way to get the data I thought I needed. Someone suggested that I go down the route of using PowerShell to ascertain the info I'm looking for.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
One of the greatest tragedies in life is to lose your own sense of self and accept the version of you that is expected by everyone else.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply