locked
Simple question: How to deny users in a role even the ability to script out stored procedures, functions, etc. RRS feed

  • Question

  • Hello,

    How can I revoke or deny access to users in a role the ability to script out stored procedures or functions?  It's ok if they see a list of them (or not) but I don't want them to see the code.  Thanks!

    Thursday, April 25, 2013 10:36 PM

Answers

  • You can deny the user the VIEW DEFINITION permission (DENY VIEW DEFINITION ON OBJECT::proc1 TO Joe;). More privileged users can grant and deny that permission that permission to others. This will work well if you maintain control of the computer and the SQL Server. (E.g. You are a bank.)

    You can create the procedure by using the ENCRYPTION option. This is some protection for a vendor who is selling an application to be installed on computers owned by others, but it's not rock solid. From the CREATE PROCEDURE topic in Books Online http://msdn.microsoft.com/en-us/library/ms187926.aspx

    ENCRYPTION               

    Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users who can either access system tables over the DAC port or directly access database files. Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

    You can't keep administrators from figuring things out, but your license can protect your intellectual property.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, April 26, 2013 3:09 PM

All replies

  • See this:

    http://msdn.microsoft.com/en-us/library/ms187719.aspx

    http://msdn.microsoft.com/en-us/library/ms173724.aspx

    http://msdn.microsoft.com/en-us/library/ms189121.aspx

    Regards,


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    Friday, April 26, 2013 12:52 AM
  • Thank you but I was looking for a specific answer about whether it's possible to deny ability to see stored procedures.  I should have been more specific in my question.  I would like to deny the ability to script out all stored procedures.  Should I move this to the beginner's forum as it's a simple question and I'm hoping for a detailed answer such as :  yes, it's possible and here's the command for this specific case.

    Thank you.

    Friday, April 26, 2013 11:24 AM
  • You can deny the user the VIEW DEFINITION permission (DENY VIEW DEFINITION ON OBJECT::proc1 TO Joe;). More privileged users can grant and deny that permission that permission to others. This will work well if you maintain control of the computer and the SQL Server. (E.g. You are a bank.)

    You can create the procedure by using the ENCRYPTION option. This is some protection for a vendor who is selling an application to be installed on computers owned by others, but it's not rock solid. From the CREATE PROCEDURE topic in Books Online http://msdn.microsoft.com/en-us/library/ms187926.aspx

    ENCRYPTION               

    Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users who can either access system tables over the DAC port or directly access database files. Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.

    You can't keep administrators from figuring things out, but your license can protect your intellectual property.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, April 26, 2013 3:09 PM