I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best as I can. Our ERP allows me to block certain fields/columns from users through their security tabs. Sounds great. However, if a person uses an ODBC or DSN connection they would be able to change data. As an example I don't want the user to be able to change costs or anything financial in a customer order table, but have no issue with him correcting incorrect addresses or shipping information. If I lock the table as READ ONLY then using the ERP would also block that column. I hope i'm making sense here. Thank you!
2
- mssqltips.com/sqlservertip/2124/…McNets– McNets2019-01-03 18:47:58 +00:00Commented Jan 3, 2019 at 18:47
- i'm not sure there's enough information here. in a normal situation, what tool would the users be using to modify data?DForck42– DForck422019-01-03 18:55:01 +00:00Commented Jan 3, 2019 at 18:55
Add a comment |
1 Answer
You can revoke update on that column from a particular user:
REVOKE UPDATE ON OBJECT::Employees.Salary FROM theUser; answered Jan 3, 2019 at 19:01
Lennart - Slava Ukraini
23.4k3 gold badges35 silver badges73 bronze badges