In SQL Server, I have a table where after a row has been inserted, several of the columns never need to be changed (yes it's possible this might change in the future, but unlikely or will be a rare event). I know that SQL server has column level permissions, and so I wanted to deny updated on these columns for all users, role, current and present. The T-SQL looks something like this:
DENY UPDATE ON dbo.<Table>(<Column1>,<Columns2>,etc) TO ______
However it appears that using this command, you have to always specify a user or role at the end. Is there a way to have it apply to all users current and future?
And yes, I AM WELL AWARE I can use a trigger to throw an error if the columns are changed after an update, or apply the DENY command to all existing users and roles and create a procedure for creating new users and roles that would create the new user/role and automatically apply the update, etc.
Before I consider any of those options, I would see if the DENY Update to all Users option can be done.
Thanks you!
Enrole all your SQL users in a default role, so that you can do a DENY on that role. For the future one, create a DDL trigger that fire CREATE USER and add it as a member of this role.