SQL Server › Views

How do you use a view to help enforce a basic level of security by limiting which columns or rows different groups of users can see?

Intermediate

Answer

You create a view that only selects the specific columns and rows a particular group of users should be allowed to access, then grant that group permission on the view itself rather than the underlying table directly, meaning they can only ever see and interact with the data exposed through that carefully controlled view.

CREATE VIEW dbo.PublicCustomerInfo AS
SELECT CustomerId, Name, City FROM Customers;

GRANT SELECT ON dbo.PublicCustomerInfo TO CustomerServiceRole;

Real-world example A company grants its customer service team access only to a view exposing basic, non sensitive customer information, while keeping direct access to the full customers table, including sensitive financial details, restricted to a smaller group of authorized users.

Common follow-ups: Is using a view for security purposes as strong as more advanced features like row level security?;Can permissions on a view be more restrictive than the underlying table's permissions?

SQL Server Security & Permissions;Dynamic Data Masking