Menu
I often read that one purpose of a VIEW is security: to allow some users access to the underlying table, and other users to a derived view only. With that in mind I designed several views that supply restricted datasets to external users.
All very fine, but in practice this doesn't work. After I grant SELECT permission on a view, the users can't access it unless I grant SELECT on all underlying objects too. Same story for stored procedures. The net result is non-functional, for I end up still granting access to sensitive data to the wrong users, as well as annoying, for it is easy to forget one object and the users come back to complain that the view "doesn't work".
Is there a way to grant SELECT permissions on a view or stored procedure without having to expose the underlying objects too?
357
For the existing view, you can go to the Properties of the view in SSMS, add users in the Permissions, and then grant select permission in the permissions list. Or use the following statement to grant user permissions: GRANT SELECT ON OBJECT::[schema]. [yourview] TO User1,User2.
Members of the db_datareader fixed database role can read all data from all user tables and views.
We can use View Definition permission in SQL Server to allow users to view the object definitions. We can either provide this access to a public role or an individual user.
Does the same user who owns the view also own the underlying tables? If not, the owner of the tables needs to grant the view owner permission WITH GRANT OPTION. If the same user owns both the tables and the view, then granting permission on the view should be sufficient.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
