October 18, 2013 at 2:10 am
I got a SA credential for connecting to clients other development partner DB, since SA is system admin privilege i found it strange that Stored procedure in the DB are locked(View definition). I can see the table structure but not stored procedure. I even tried to give VIEW DEFINITION permission by clicking instance properties but no effect.
I just want to know is it possible to DENY VIEW DEFINITION to user with SA privellege, how to implement that?
How to overide this type of permission so that i can view the proc.
October 18, 2013 at 2:52 am
What message do you get if you run
EXEC sp_helptext 'ProcName'?
John
October 18, 2013 at 2:58 am
John Mitchell-245523 (10/18/2013)
What message do you get if you run
EXEC sp_helptext 'ProcName'?John
this is the message i'm getting The text for object 'procname' is encrypted.
October 18, 2013 at 3:00 am
OK, then it's nothing to do with permissions. You won't be able to see the procedure definition unless you have the source code.
John
October 18, 2013 at 3:05 am
John Mitchell-245523 (10/18/2013)
OK, then it's nothing to do with permissions. You won't be able to see the procedure definition unless you have the source code.John
Indeed. There's a big difference between "being locked" and "being encrypted".
Stored Procedure WITH ENCRYPTION and Execution Plan[/url]
When using a DAC connection, you should be able to decrypt it though (the encryption is not that safe):
Decrypting encrypted stored procedures, views, functions in SQL server 2005,2008 &R2[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 3:08 am
John Mitchell-245523 (10/18/2013)
OK, then it's nothing to do with permissions. You won't be able to see the procedure definition unless you have the source code.John
thanks for the clarification.
Is is not a performance issue if we create our proc with encryption
October 18, 2013 at 3:12 am
I don't think it's a performance issue, but it is a maintenance nightmare. Some third-party vendors encrypt their stored procedures so that you can't copy or change their code.
John
October 18, 2013 at 3:13 am
SQL006 (10/18/2013)
John Mitchell-245523 (10/18/2013)
OK, then it's nothing to do with permissions. You won't be able to see the procedure definition unless you have the source code.John
thanks for the clarification.
Is is not a performance issue if we create our proc with encryption
Not really. Once the plan is compiled and stays in cache, it doesn't need to be decrypted.
Maybe there's a negligible impact on recompiling.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 18, 2013 at 3:14 am
John Mitchell-245523 (10/18/2013)
I don't think it's a performance issue, but it is a maintenance nightmare. Some third-party vendors encrypt their stored procedures so that you can't copy or change their code.John
Because they are soooo difficult to decrypt 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply