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

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.
bijualexAsked:
Who is Participating?
 
srnarConnect With a Mentor Commented:
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
 
bijualexAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
James MurrellProduct SpecialistCommented:
0
 
bijualexAuthor Commented:
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
 
bijualexAuthor Commented:
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
 
bijualexAuthor Commented:
Many new things to be learned....Thank you !!!!
0
 
srnarCommented:
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
 
bijualexAuthor Commented:
Perfect - Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.