January 14, 2017 at 6:18 am
I have a mission critical table which at times unfortunately does need a manual update to key value. Failure to remember the obviously not so obvious fact that you do absolutely need that WHERE clause which is unfortunately so easy to forget when every single one of those mission critical applications have suddenly convinced all the users that the IT Director finally got the one guy with the password to walk out. Could RowCount 1 somehow be used to help?
January 14, 2017 at 6:54 am
mary.clemons 79057 - Saturday, January 14, 2017 6:18 AMI have a mission critical table which at times unfortunately does need a manual update to key value. Failure to remember the obviously not so obvious fact that you do absolutely need that WHERE clause which is unfortunately so easy to forget when every single one of those mission critical applications have suddenly convinced all the users that the IT Director finally got the one guy with the password to walk out. Could RowCount 1 somehow be used to help?
No, because the person writing the UPDATE would have to remember to put SET ROWCOUNT 1 before the update (and if they're careful enough to remember that, they can just put the WHERE clause there)
Also, ROWCOUNT for INSERTs, UPDATEs and DELETEs has been deprecated since SQL 2012 I believe.
Couple options:
Instead of giving the users update rights on the table, write a procedure that does the necessary updates with a WHERE clause and give whoever's fixing this just EXECUTE rights on that procedure
If all users are sysadmin, maybe an update trigger that checks @@Rowcount (number of rows affected by the update) with the number of rows in the table and rolls back (with a 'You forgot the WHERE clause" error if they're the same)
First one is a better solution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2017 at 9:55 am
mary.clemons 79057 - Saturday, January 14, 2017 6:18 AMI have a mission critical table which at times unfortunately does need a manual update to key value. Failure to remember the obviously not so obvious fact that you do absolutely need that WHERE clause which is unfortunately so easy to forget when every single one of those mission critical applications have suddenly convinced all the users that the IT Director finally got the one guy with the password to walk out. Could RowCount 1 somehow be used to help?
You could write a trigger that would rollback the transaction if the row count exceeds a threshold.
January 17, 2017 at 12:37 pm
Joe Torre - Tuesday, January 17, 2017 9:55 AMmary.clemons 79057 - Saturday, January 14, 2017 6:18 AMI have a mission critical table which at times unfortunately does need a manual update to key value. Failure to remember the obviously not so obvious fact that you do absolutely need that WHERE clause which is unfortunately so easy to forget when every single one of those mission critical applications have suddenly convinced all the users that the IT Director finally got the one guy with the password to walk out. Could RowCount 1 somehow be used to help?You could write a trigger that would rollback the transaction if the row count exceeds a threshold.
I also had this same thought for a brief moment, but triggers are not fired for truncate table statements, and there may occasionally be a legitimate need to delete or update more than (x) rows. I second the motion to remove users from SYSADMIN, DBO, or DATAWRITER roles and instead grant them execute permission on a parameterized stored procedures which contains the proper logic. If end users can ad-hoc write to the database, there are 100 different ways they can screw up the tables. For example, they can open an SSMS connection to production by mistake thinking it's development, or they can get the source and destination servers switched around when attempting to import data from tables in production to QA.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply