| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2012-11-14 : 17:41:24
|
| Hello there.At work i have a list of stored procs that all have a padlock next to them. They owned by a schema that i do not work with.I think they have been created by an external vender.But how do or where can i see where the restrictions are in SQL server.RegardsRob |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-14 : 19:43:51
|
| That means you don't have all the privileges on those stored procedures. You probably have only execute permission. If you right click on the stored proc in SSMS object explorer, select properties and then select permissions, you will see what kind of permissions you have. You probably cannot script the stored procedure to see the code. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 03:37:05
|
| Umh, I think it means they are "encrypted". I put encrypted in quotes because the encryption is allegedly really easy to crack. The vendor is probably trying to prevent you from seeing the source code...- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 04:08:08
|
| NO not able to see script.I am the sysAdmin for the server.. how do i un encrypt a stored proc in a legitimate way |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 05:54:35
|
quote:
Originally posted by masterdineen
NO not able to see script.I am the sysAdmin for the server.. how do i un encrypt a stored proc in a legitimate way
You call the vendor and ask them to see the source code...- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 06:47:27
|
| so once you have encryped an object, there is no way back unless you get hold of some software to unlock.is that the case? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 07:42:04
|
| Have you read your own question? The only way to decrypt something that is encrypted is to either have the decryption algorithm or to hack it. And noone here will help you hack it (at least they shouldn't!!) so that leaves you with two options (three actually); ask the vendor for the sourcecode (as per my initial suggestion) or to figure out how to hack it yourself. Last option is to ignore it and leave whatever problems might arise to the vendor.- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-15 : 08:49:09
|
quote:
Originally posted by Lumbago
Umh, I think it means they are "encrypted". I put encrypted in quotes because the encryption is allegedly really easy to crack. The vendor is probably trying to prevent you from seeing the source code...- LumbagoMy blog-> http://thefirstsql.com
Clearly not the case here because OP has sysadmin privileges, but in my SQL 2008, if a user has only execute permission on a stored proc, it shows up a with a lock symbol next on the icon, although a smaller one. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 08:50:49
|
| In my latest post i asked. Once you have encrypted an object, there is no way back? or is it the case that the only person who encrpted it can decrypt the object. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-15 : 08:53:49
|
| If you encrypt, other than the approaches that Lumbago suggested, there is no way to see the code. In fact, even the person who encrypted it cannot. So people make sure that they take a copy of the code before encrypting. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 08:55:49
|
| ok thank you. what about altering objects when encrypted? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-15 : 09:10:24
|
| You can't do that either. To alter, you would first have to see the code for the stored proc. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 09:23:00
|
| so if you cant alter it, what can you do?. even if you can see a copy of the code. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-15 : 09:30:30
|
| You can execute encrypted stored procedures and functions, and select/insert/update/delete from encrypted views. You don't need to see the source code to do that.FWIW every stored procedure I've decrypted has been some of the worst SQL I've ever seen. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-15 : 09:34:09
|
| You probably can execute the stored procedure. More often than not, in databases that are properly set up, an end-user is given only execute permissions. The thought process being that making any changes has to be a well-controlled process done by the database vendor after a lot of requirements reviews, code development, testing, quality control etc. So it is not unusual that you are not able to see or alter the stored procedures.If you right-click on the stored procedure in SSMS object explorer, select properties and look in Permissions tab, you should be able to see what kind of permissions you have. One of them is execute. (Another one is View definition, but if the stored proc is encrypted, even if you have that permissions, you won't be able to see the definition). |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-11-15 : 09:34:13
|
If it is encrypted, you can't alter it. You can only execute it.If you can see the code, it is not encrypted. And you can alter it.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 09:37:49
|
| Yes I can imagine. what i don’t understand is. if you want to hide away code of a stored proc you have to encrypt it, but if you want to alter / drop or whatever. cant decrypt it again with a certificate or something. rather useless featureSo at the end of the day. you only encrypt when you are totally finished with the SP or function you are writing. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-15 : 09:46:35
|
quote:
rather useless feature
Right on the money! And also in line with the common perception among sql server professionals that you never encrypt your procedures, and if you do it's probably because it's shit.- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2012-11-15 : 09:48:37
|
| Brilliant. Thank you for clearing that up everyone. Is there any improvements on this in 2012? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-15 : 11:04:04
|
quote:
cant decrypt it again with a certificate or something. rather useless feature
If you want reference copies of code, use source control. The database is a deployed product. You can't reverse engineer source code from a compiled executable file either, so I don't understand the confusion. I do agree that in its current form it's useless, but only because it's easily reversible.quote:
Is there any improvements on this in 2012?
Nope. The backwards-compatibility gods at Microsoft would be angry. You can apply certificate-based signatures to CLR modules that contain stored procedures, but I don't believe this extends to T-SQL stored procedures. |
|
|
|
|
Padlock next to stored procedures