asked on

SQL Permission to run view only

I need to configure a user to run only a view and nothing else on the server (SQL Server 2008 R2). They should have permission to run the view and see the data it returns but not have permission to the underlying tables. Lets say the view is called TestView and the user is called TestUser. What SQL script can be run to configure this user with permissions to only run the view which exists in a database called TestDatabase?

Any help would be appreciated.

Thanks...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jorgedeoliveiraborges

grant select on dbo.view_name to UserBD_Name;

jorgedeoliveiraborges

grant select on dbo.view_name to UserBD_Name; 
revoke select on dbo.view_name from UserBD_Name;

Open in new window

AlwaysAStudent

ASKER

Hi all,

Thanks for the replies - I have granted select permission to the SQL view but when that user runs it, it returns no results. Running the same view with another user returns the results I'm expecting.

Guy Hengel [angelIII / a3]

then you are presumably not using the same view ...
please check by specifying the views owner in the select:

SELECT * FROM view_owner.view_name

SOLUTION

Anthony Perkins