In what scenarios would I need to use WITH ENCRYPTION?

I appreciate that you can create views WITH ENCRYPTION so that you are not able to access the SQL code by running EXEC sp_helptext 'ViewName' but I am not sure when you would be required to restrict access like this?

Surely prying eyes would be locked down with the correct permissions set on the server and blocking of the running of the sp_helptext stored procedure just for starters?

more ▼

asked Feb 23, 2010 at 07:29 PM in Default

avatar image

Ian Roke
1.7k 32 35 38

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I'm going to disagree with many of the answers here and say there are times it is useful, but rarely. If for instance you have no choice but to hard code a password.

I already hear the objections. Yes, you should never need to do that if the security in other locations is set up properly. But sometimes a DBA does not have control of the security outside the database. Perhaps the network admin that the DBA has no control over set up the accounts in such a way a password must be used. Or perhaps you are dealing with a 3rd party solution whose security was not set up well and you have to pass a password as a parameter. I will not go into details, but I have on 2 occassions been forced to hardcode passwords in T-sql code.

Yes, I know the encryption is readily broken and the security is minimal. But often you know a lock can be picked by anyone with a little determination and a little skill (or just broken by someone with a decent bolt cutter) and still the physical lock is useful because it establishes that barrier to entry. Like many of those insecure physical locks, it isn't so much meant to actually stop someone who really wants to get in, but to stop the idly curious. It says you know you are doing something wrong if you cross this line. It puts just enough of a barrier to entry to ensure that anyone who reads it is deliberately hacking and not poking around to learn the system. Sometimes, that is quite enough if you trust the people around you and don't want them to idly stumble over that password or other sensitive information.

So, if you do use it, do so realizing that you probably shouldn't have to do so (at least not long term) and that the security is minimal. But it is useful when that minimal security is enough.

more ▼

answered Feb 23, 2010 at 08:51 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left


The encryption used is easily broken by anyone who can use google, and it will just mean that you get less sensible error reports from users who are capable of diagnosis of more complex errors.

So, you won't stop people who really want to pry, and will annoy people who don't.

more ▼

answered Feb 23, 2010 at 07:54 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

But what if your code was being deployed on someone else's server, i.e. you wrote software that depended on a SQL Server data platform, but you didn't want to share your source sql?

more ▼

answered Feb 23, 2010 at 07:37 PM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

Why would I want to hide the code though? A view is surely essentially just a select statement there is no personal data or passwords set in there. They could run the same thing manually if they worked it out.

Feb 23, 2010 at 07:39 PM Ian Roke

Since it takes only a few moments to circumvent the encryption it is basically worthless for protecting IP. It achieves nothing except to obstruct the customer's DBA in doing his proper job when he wants to review the code.

Feb 23, 2010 at 08:05 PM David 1

I don't think this is a valid reason. Your source code, honestly, is likely not as important as you think. If it is, then you need a hosted solution and do not put it on someone else's server.

Feb 23, 2010 at 10:07 PM Steve Jones - Editor ♦♦

All are valid points. I don't know WHY you would want to do it, and I have never done it - just thought this may be a reason. I guess encrypting a view definition, and nothing else is pretty futile...

Feb 24, 2010 at 05:01 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Never. There are no circumstances in which it makes sense to use this feature.

more ▼

answered Feb 23, 2010 at 08:01 PM

avatar image

David 1
1.8k 3 5

+1, avec deja vu...

Feb 23, 2010 at 08:09 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 23, 2010 at 07:29 PM

Seen: 1515 times

Last Updated: Feb 23, 2010 at 07:29 PM

Copyright 2018 Redgate Software. Privacy Policy