SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Padlock next to stored procedures
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/14/2012 :  17:41:24  Show Profile  Reply with Quote
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.

Regards

Rob

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  19:43:51  Show Profile  Reply with Quote
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.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  03:37:05  Show Profile  Reply with Quote
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...

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  04:08:08  Show Profile  Reply with Quote
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

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  05:54:35  Show Profile  Reply with Quote
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...

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  06:47:27  Show Profile  Reply with Quote
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?
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  07:42:04  Show Profile  Reply with Quote
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.

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/15/2012 :  08:49:09  Show Profile  Reply with Quote
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...

- Lumbago
My 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.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  08:50:49  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/15/2012 :  08:53:49  Show Profile  Reply with Quote
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.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  08:55:49  Show Profile  Reply with Quote
ok thank you. what about altering objects when encrypted?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/15/2012 :  09:10:24  Show Profile  Reply with Quote
You can't do that either. To alter, you would first have to see the code for the stored proc.
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  09:23:00  Show Profile  Reply with Quote
so if you cant alter it, what can you do?. even if you can see a copy of the code.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/15/2012 :  09:30:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/15/2012 :  09:34:09  Show Profile  Reply with Quote
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).
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/15/2012 :  09:34:13  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  09:37:49  Show Profile  Reply with Quote
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 feature

So at the end of the day. you only encrypt when you are totally finished with the SP or function you are writing.
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/15/2012 :  09:46:35  Show Profile  Reply with Quote
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.

- Lumbago
My blog-> http://thefirstsql.com

Edited by - Lumbago on 11/15/2012 09:47:05
Go to Top of Page

masterdineen
Aged Yak Warrior

United Kingdom
549 Posts

Posted - 11/15/2012 :  09:48:37  Show Profile  Reply with Quote
Brilliant. Thank you for clearing that up everyone. Is there any improvements on this in 2012?
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 11/15/2012 :  11:04:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000