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!


You can revoke update on that column from a particular user:

REVOKE UPDATE ON OBJECT::Employees.Salary FROM theUser;

See: https://docs.microsoft.com/en-us/sql/t-sql/statements/revoke-object-permissions-transact-sql?view=sql-server-2017

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.