Search code examples
sql-serverprocedure

Deny Read-Only Users to see DDL of a Stored Procedure MS SQL

Several read-only users have Management Studio and run their own queries, which is not a problem. However, I would like to protect the code of the Stored Procedures and views so that people can't see the code behind them by right clicking on the object and selecting Script Procedure As>Create to>New Query Window.

I tried adding WITH ENCRYPTION, but this block even the DBOs from seeing the code. So that is not a good solution.

Is there any other way that I could accomplish this?

Solution

  • Another option is to deny VIEW DEFINITION permission. The following post goes into detail on this feature.

    https://www.mssqltips.com/sqlservertip/1828/options-for-hiding-sql-server-code/

    See section "ALTERNATE TO USING WITH ENCRYPTION CLAUSE".