Search code examples
sql-serversql-server-2017

Create a read-only column that can get updated only by code

Is there a way to create a column to be readonly to the user, but updatable by the script/code?

i know with update statement whatever values the users will put will get overwritten anyways, but it would be nice to make it read-only to the user in the first place.

for example, if i want to create a PROCESS_STATUS column, in which the script inserts success or failure status as value, and this value is read-only to the user of course

Solution

  • Create a VIEW of the table. Grant your users read-only access to the view, and no access at all to the table.

    This is exactly the sort of thing that Server and Database Roles exist to manage.