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!
Sql-server – Prevent a user from making changes to some columns in a MS SQL Server database
database-designlockingsql server
Related Question
- Mysql – The total number of locks exceeds the lock table size, even after increasing buffer pool size
- Sql-server – When to use CDC to track history
- Sql-server – Deploying SQL Server database changes from test to production server
- Database Design – Storing Personal Data on Invoices
- PostgreSQL – Prevent Columns from Having Same Value in Record
- Sql-server – SQL Server Audit Changes in user permission
- How to model local exceptions to distributed tables
- MySQL 8.0 – Metadata Lock Extension for Foreign Keys Causing Requests to Hang
Best Answer
You can revoke update on that column from a particular user:
See: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-object-permissions-transact-sql?view=sql-server-2017