code view access

Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA

In SQL Server 2005 this should be the default behavior. Please refer to BOL for further details:

http://msdn2.microsoft.com/en-us/library/ms191507.aspx

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

If you have further question please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul, we are actually using SQL 2000 SP4 (8.00.2040 build) in all our environments.

Do we need to DENY access from sp_helptext/syscomments or any other objects? Any thoughts are appreciated. Thanks.

|||

Unfortunately, there is really not a way to do that with SQL 2000 -I know, big time bummer.

If a user has permissions to execute the object, the user can see the code for the object -unless it is encryped (and SQL 2000 code encryption is very lame...) The concept of separating permissions for viewing the code and executing the code came about in SQL 2005.

|||Thanks, wouldn't protecting sp_helptext and syscomments resolve this? I guess whenever code is opened through any util, it must be querying syscomments either using sp_helptext / DMO in SQL 2000. Am I incorrect on this assumption?