Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select permission failed on underline object in Views Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 12:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
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?

I did the following things but still getting error message like "Select permission was denied on the object" and this object is a table which is on different databases.


1. Enabled the ownership chaining.
2. Views and tables owners are dbo.


Any help would be appreciated.




Post #1446664
Posted Thursday, April 25, 2013 12:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:55 PM
Points: 23,043, Visits: 31,567
If you are crossing databases the user needs access to the table(s) in the other databases.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1446665
Posted Thursday, April 25, 2013 12:33 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 25, 2013 8:08 AM
Points: 490, Visits: 1,383
These views are filtered views based on the tables and tables contains all more info. If I give access to the table then user will be able to see all the data.



Post #1446673
Posted Thursday, April 25, 2013 3:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 26, 2014 12:23 PM
Points: 258, Visits: 808
I believe, that if ownership chaining is good and the explicit dbo is the same in both databases,
then all that is needed is for the login to be a user in the database where table(s) are located,
in addition to of course having select permission on the view in the database where the view is located.
Post #1446728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse