| Author |
Topic |
|
tmcivery
Starting Member
46 Posts |
 Posted - 2010-08-16 : 10:57:43
|
| Hey all,I recently upgraded a program that has "re-locked" a SQL View that a consultant had somehow previously opened an modified. The view now shows as locked or encrypted(I don't know which or if it matters) and I can not edit it to modify the code inside. Is there a way that someone can explain that I can edit this "locked" view? I know it can be done as the backed up SQL database(from before the upgrade has this particular view and it is the only view that is able to be edited.Any guidance is appreciated.Tony |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tmcivery
Starting Member
46 Posts |
Posted - 2010-08-16 : 11:45:58
|
| Well, it is a view in a database that shows a little lock icon on it and subsequently will not allow you to choose "Edit" when right clicking on it. I don't know how to explain it any differently. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-16 : 11:49:06
|
| its because its encrypted. If its encrypted then there might be a good reason for doing that. why do you want to unlock it? are you developer or DBA?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tmcivery
Starting Member
46 Posts |
Posted - 2010-08-16 : 11:57:09
|
| I am neither a developer or a dba. We had a dba that had modified this particular view in order to add a field to it that allowed us to integrate the program that uses this database with another program. I upgraded the program that uses this view and the view was set back to default by the upgrade. I have the code that the dba created to change the view(which worked perfectly fine before the upgrade) but being that the view is encrypted again I can't edit the view. I'm competent enough in SQL to make this change if I could get in to do so. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-16 : 12:21:45
|
| http://www.sqlmag.com/article/encryption2/decrypt-sql-server-objects.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
tmcivery
Starting Member
46 Posts |
Posted - 2010-08-16 : 14:16:04
|
| Sorry for wasting your time guys but I just assumed it would be more difficult than it really was.I can understand your being skeptical but I've figured it out on my own(simply rename the original view, create a new one and populate the design table with the necessary t-sql code and name it the original view's name). This method doesn't require you to somehow decrypt the encrypted view(if that's even possible). Sorry if I wasn't clear enough about what I was trying to accomplish but everything is now in working order. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-08-16 : 14:50:23
|
if you're dbo (or db_ddladmin, i believe) you can rename it.create view EncryptedViewWith EncryptionASSELECT 1 as eeeGOexec sp_rename EncryptedView, RenamedEncryptedViewGO |
|
|
|
|
Unlock a SQL View in SQL 2005
