[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Permission to run view only

Posted on 2011-10-19
7
Medium Priority
?
567 Views
Last Modified: 2012-05-12
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...
0
Comment
Question by:AlwaysAStudent
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 36993171
you create the login, mapped to a user in that database, and grant just the SELECT for that view.
while the user might see names of databases, and eventually of tables or view, he cannot query any other actual table or sql.
for the list of database, you can do:
REVOKE VIEW ANY DATABASE TO <login>;

0
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 36994115
grant select on dbo.view_name to UserBD_Name;
0
 
LVL 6

Expert Comment

by:jorgedeoliveiraborges
ID: 36994136
grant select on dbo.view_name to UserBD_Name; 
revoke select on dbo.view_name from UserBD_Name; 

Open in new window

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 1

Author Comment

by:AlwaysAStudent
ID: 36994173
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.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36994513
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

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 36997260
Or (God forbid) you have defined the user as a condition in the VIEW.
0
 
LVL 1

Author Closing Comment

by:AlwaysAStudent
ID: 36998236
Thanks for everyone's help - it was a two part thing. After granting the view and finding that it still didn't work, I looked at what the view required, which was another view. That view was a standard CRM view which filtered records based on permissions.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question