• A good, solid question. Too bad it's only worth 1 point, I think 2 points would have been called for here.

    tilew-948340 (3/10/2012)


    Since then, I put only "SELECT" permission on the views to make sure that nobody messed up with the data base without knowing it!

    Many people do the reverse - they give nobody any permissions on the base tables and give permissions on views only. For insert and delete, that does not really change much, but for update and select, it's a good way to show only relevant information. For instance, if your table contains a column is_active and you have a view that includes WHERE is_active = 1 and does not include columns that are only relevant to non-active members, you would give permissions on that view to the relevant users and they would never be able to see data about inactive members - neither the members, nor the columns that are relevant for inactive members.

    And if the view includes the WITH CHECK option, those people would also be unable to change active members to non-active members.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/