Solved

How to create an sql user with access to a view but not to the view definition

Posted on 2008-06-15
9
275 Views
Last Modified: 2012-05-05
We have our database on sql server 2000. We are having some new software implementation and as part of that we are supposed to provide some data to the new vendor from our sql database.

We are planning to provide the data in a new view created in a different sql 2000 server. This view will be accessing the original server using openrowset and providing the necessary data. How can we create a user in the new sql server who has access only to the data in this view? The user should be able to retrieve the data from the view but should not be able to see the definition of the view ("sp_helptext viewname" should not work). This is to make sure the original server name is not exposed to the third party.
0
Comment
Question by:bijualex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21788702
0
 

Author Comment

by:bijualex
ID: 21791765
Hi cs97jjm3, thanks for the details. This page gives information about creating views and related things but what I want is to create a user which can only select data from this view and cant see the definition of the view (this user need not do anything in the database except doing a select stmt on the view)
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 21791981
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:bijualex
ID: 21792258
This again talks a lot about accessing permissions on tables and views but doesnt address my issue. I will re iterate my requirement - user should not be able to write "sp_helptext viewname" and see the select statements written in the view. Is this possible?
0
 
LVL 8

Accepted Solution

by:
srnar earned 500 total points
ID: 21792448
The best solution is to create your view with encryption. No user will be able to see its source code.

There is a general solution how to disable the sp_helptext but with huge impact - no regular user (perhaps except sysadmins) - will be able to see any source codes. I do not recommed it!!!

There are also similar threads here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1569739&SiteID=1

and here
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20331218.html


--encryption
CREATE VIEW aView
WITH ENCRYPTION  
AS
SELECT 0 Col0
 
sp_helptext 'aView'
 
--sp_helptext
USE [master]
 
DENY EXECUTE
ON sp_helptext
TO PUBLIC

Open in new window

0
 

Author Comment

by:bijualex
ID: 21792722
srnar - Excellent, I tried both the ways - DENY EXECUTE ON sp_helptext TO username, though prevented the user from doing sp_helptext, through enterprise manager the user could see the code. So I think I need to go with the ENCRYPTION option. Thanks for the help, another small question - is there any way for the sa user to retrieve the encrypted code or we need to keep this saved in a separate file? Thanks.
0
 

Author Closing Comment

by:bijualex
ID: 31467347
Many new things to be learned....Thank you !!!!
0
 
LVL 8

Expert Comment

by:srnar
ID: 21793019
Yes - administrator can use this utility ( there are some ways how to get the encrypted code) - but I strongly recommend to have source code externally - you can use e.g. Microsoft Source Safe for its versioning.

Decrypt utility:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5

Your restricted user should not be able to run ALTER VIEW required by DECRYPT utility.
0
 

Author Comment

by:bijualex
ID: 21793367
Perfect - Thank you.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 64
Moving away from Access 2003 adp files 4 48
SQL State HYT00. Timeout expired proplem 8 44
What does "Between" mean? 6 47
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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