Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not using the stored procedures designed to update it.

I am considering implementing this using triggers and a token table. The idea i am considering is to have a token table. the stored procedures will have to first insert values in the token table. Then update the price columns. The update trigger will check if the token exists in the token table for the updated row. if found, it would continue. if the token is not found, it will throw an exception and make the update transaction fail.

Thanks Michal, but this solution won't work for me because, my application is a web application that is using connection pooling. All users connect using the same SQL Server Connection String.
–
EliasSep 22 '12 at 15:01

@Elias I don't understand. The connection string connects as a specific user, right? So replace SampleRole with that user...
–
Aaron Bertrand♦Sep 22 '12 at 17:29

column level permissions are the way to go here. That and ensuring that the developers know that making changes to the values via t-sql directly will destroy the system.
–
mrdenny♦Sep 23 '12 at 2:55

You don't need to create a new user to do this...
–
Aaron Bertrand♦Sep 22 '12 at 17:34

@aaronbertrand you're right - for so,e reasonni was thinking you'd do "execute as creator", but that's not a thing - you can do "execute as owner" as long as the user that owns the schema had rights to update that table. If the stored proc is in dbo, then you're covered. I'll update my answer.
–
SqlRyanSep 22 '12 at 19:10