Solved

How to lock stored procedure

Posted on 2002-06-11
11
1,665 Views
Last Modified: 2008-01-09
Hi,
    May i know is there any method we can lock the stored procedure, even for the DBA (system administrator), where just the programmer can view the stored procedure only...

thnx,
sityee
0
Comment
Question by:sityee
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 7

Expert Comment

by:lozzamoore
ID: 7069468
Can't think of a way to disable a proc directly.

How about as a workaround you add:

sp_helptext <proc name>
return

to the top of your stored proc?

Cheers,
0
 

Expert Comment

by:AdiCohn
ID: 7069483
Hi Sityee

There is no way to hide any object from the DBA.  The DBA can see all objects and he can modify all the objects.  If you want to prevent the DBA from looking at the stored procedure,  you can build it with an encryption.  This way no one will be able to see the stored procedure's code.  Be aware that the developer won't be able to see it,  so he must store the procedure's code somewhere else.  One more thing that you'll need to take into consideration is that I had stored procedures that I found on the net that broke the encryption on 6.5 and 7 versions.  I haven’t looked for something that brakes the encryption in 2000,  but if there isn't one yet,  then it is just a matter of time.  In short,  the encryption in SQL SERVER is not a good thing..  
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 7070282
The only thing I would use is to encrypt the stored procedures. This way, the developer should have the script to create/alter the stored procedures, but the proc is only in compiled form, even the sp_helptext won't be able to return the sql:

CREATE PROC yourproc
WITH ENCRYPTION
AS
...
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 7

Expert Comment

by:lozzamoore
ID: 7070297
Sorry, my suggestion answers a different, probably misinterpreted requirement.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7070322
hi AdiCohn:
I welcome you at EE.
As you locked the question, I would like to point to the EE guidelines about comments vs answers (see bottom of page). Although your comment is very good and you deserve the points, you should leave the choice to the questioner. I have seen many times my "good comment" to be the wrong answer, simply because the questioner wanted something else...

CHeers
0
 

Expert Comment

by:AdiCohn
ID: 7070724
Hi Angellll and all

  Sorry I should have written it as a comment.  Next time I'll do it the proper way

Adi
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7072058
Thanks
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7134642
if @@USER <> 'SA' then return.
0
 
LVL 7

Expert Comment

by:lozzamoore
ID: 7137243
That doesn't work in SQL 7.

Alternative is:
if (select user_name())<>'dbo' return
0
 

Expert Comment

by:modulo
ID: 7427226
Dear: AdiCohn

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7485897
you can always make one of the parameters of the SP as a password--

in other words-- you say

create proc myProc
@password @varchar(25)
@firstLetter
as

select * from employees where Left(firstName, 1) = @firstLetter and @password = 'mysupersecretpassword'

then as long as no one can read the definition for the SP, then you have to throw the password into it to make it work.

if you want to get really high tech, you can make the last whereclause into something more like this:

left(@password, 15) = mysecretpassword and cint(mid(@password, 15, 2) = 100 - Day(getDate())

so then you would need to pass the password 'mysecretpassword78' today (11/22)

and that password would only work today..

that way if you needed to release the password to someone that was quasi-technical then you could just give them the password that would be good for a day..

or.. you could make some super complex algorithm up to do this...
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Re-appearing SQL Server Agent jobs 7 30
sql server query 12 26
Getting invalid Syntax SQL. 3 21
Substring works but need to tweak it 14 16
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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