2

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
    Commented 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
    Commented Jan 3, 2019 at 18:55

1 Answer 1

5

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

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

See: https://learn.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 and acknowledge you have read our privacy policy.

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