June 18, 2018 at 9:06 am
I have been asked to audit users who are pasting data into a table by right-clicking it, and choosing "Edit Top 200 Rows".
What would be the best way to audit this?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 18, 2018 at 9:36 am
Take away their access!
June 18, 2018 at 9:40 am
LOL - yeah that was my first thought...
Apparently they want to audit to see how prevalent it is so that's why I thought I'd reach out here first
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 18, 2018 at 10:05 am
Users with update access to a production table? Seriously? Sounds more like your security model needs some serious work... Never should a user have direct update access to a production table. That's pretty much a very bad idea waiting for an ideal opportunity to make things go not just bump in the night, but humpty dumpty sat his dumb-a$$ precariously on the wall and fell off because he couldn't be careful... and all the kings horses and all the kings men, couldn't put humpty dumpty back together again...
Have you looked into using roles for permissions purposes? Typically, an application role has access to the data, and users are validated by the application in some fashion, but then only the application uses that database role to touch the database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2018 at 10:12 am
We all know accessing production table data is typically a bad idea, we would all probably architect things differently if we could, and we probably all would instantly revoke their permission to simply be read only, etc...
However, "they want to audit who's doing it and how often it occurs."
I'm open to suggestions on that.
Thanks!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 18, 2018 at 10:24 am
MyDoggieJessie - Monday, June 18, 2018 10:12 AMWe all know accessing production table data is typically a bad idea, we would all probably architect things differently if we could, and we probably all would instantly revoke their permission to simply be read only, etc...However, "they want to audit who's doing it and how often it occurs."
I'm open to suggestions on that.
Thanks!
I'm doubtful that such is easily audited. Using SSMS to do that can't be audited directly in any way that I'm aware of. The only shot you probably have is to set up a trigger for AFTER UPDATE and log the changes made to an audit table that the users have NO access to. Of course, the question is why users are making use of SSMS. It suggests strongly that there may well be an "unmet need" for data that helped create this scenario in the first place. Finding out if that need still exists may be essential to putting a stop to that behavior.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 18, 2018 at 1:15 pm
MyDoggieJessie - Monday, June 18, 2018 10:12 AMWe all know accessing production table data is typically a bad idea, we would all probably architect things differently if we could, and we probably all would instantly revoke their permission to simply be read only, etc...However, "they want to audit who's doing it and how often it occurs."
I'm open to suggestions on that.
Thanks!
It wouldn't be too easy and would be very noisy...but you could capture the activity from SQL Server Management studio using Extended events or a server side trace. However the app name can be easily spoofed so it wouldn't necessarily be reliable from that perspective. But it is available and would be available triggers (APP_NAME) as Steve mentioned. One idea you may want to play around with.
Sue
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply