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?
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.
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?
Never.
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.
Never. There are no circumstances in which it makes sense to use this feature.
