Solved

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

Posted on 2008-06-15
9
273 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert into table 8 22
SQL Syntax 5 34
query execution hang 5 28
SQL Server 2012 r2 - Varible Table 3 23
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

776 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