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?