I have a user and only want them to have select access to a single view and nothing else.
I tried to create my own schema containing the view and deny access to dbo but it seems it doesn't work as I would have expected it to. I can still run queries against tables even though I can't seem them in SSMS.
CREATE SCHEMA vl CREATE VIEW vl.vw_Search AS SELECT 'This is a test' AS RESULT GRANT select ON vl.vw_Search TO vlu DENY SELECT On SCHEMA::sys To vlu DENY SELECT On SCHEMA::INFORMATION_SCHEMA To vlu DENY INSERT, UPDATE, DELETE ON schema::dbo to vlu DENY VIEW ANY DATABASE TO vlu DENY VIEW ANY DEFINITION TO vlu
I can also still access system views which I also don't want the user to be able to do.