Mark Breen
marklbreen at gmail.com
Mon Aug 17 10:02:27 CDT 2009
Hello Stuart, thanks for that, never knew you could do that. Here is the text from MSDN. 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 that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port<http://msdn.microsoft.com/en-us/library/ms189595.aspx>or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration<http://msdn.microsoft.com/en-us/library/ms187113.aspx> . This option is not valid for CLR stored procedures. Procedures created by using this option cannot be published as part of SQL Server replication. ] I would hate to have to debug a system that was encrypted like this, but I guess it is an option for some circumstances. Mark 2009/8/17 Stuart McLachlan <stuart at lexacorp.com.pg> > Yes, the Sysadmin will have access. > > In this scenario, you can create the SPs on you development system and > then script them > out. Modify the script to include "WITH ENCRYPTION" ie > > "CREATE PROCEDURE dbo.myproc > WITH ENCRYPTION > AS...." > > Then you can run the scripts on the client installation. > > -- > Stuart > > On 17 Aug 2009 at 21:35, Borge Hansen wrote: > > > Stuart, > > In the scenario where I am installing an application consisting of an > > Access/VBA Frontend and a SQL Db backend at a client's site - will that > > hold? > > > > Wouldn't the client's SQL Data Base Administrator and anyone else with > > sysadmin rights on the SQL Server have access to all the SP code that I > have > > installed? > > > > regards, > > borge > > > > On Mon, Aug 17, 2009 at 8:45 PM, Stuart McLachlan < > stuart at lexacorp.com.pg>wrote: > > > > > You use the SQL Server's built in permissions. > > > If they are not authorised to do so, they can't view the definition. > > > > > > As a general rule, users should only have Execute rights on SPs. > > > > > > -- > > > Stuart > > > > > > > > > On 17 Aug 2009 at 19:45, Borge Hansen wrote: > > > > > > > Hi all, > > > > > > > > How do I protect code in a Stored Procedure from being looked at, > messed > > > > with, lifted out and used by others - not authorized to do so? > > > > > > > > I've read you can encrypt SP code - but that there are easy ways to > get > > > back > > > > the code in a readable form. > > > > > > > > Then there is the possbility of creating the SP in an CLR assemby: > > > > "embedding" the SP in C# code and ending up with the SP in a .dll > file - > > > > etc... I think they are called "managed stored procedures" > > > > > > > > Question: > > > > Is this the way to fully protect / secure the SP code from the eyes > of > > > > others?? > > > > > > > > If so, does anyone know of a few good links that will get me up to > speed > > > in > > > > writing and presenting Stored Procedures in a SQL db as CLR > assemblies - > > > > "Managed Stored Procedures" ? > > > > > > > > I have VS2008 up and running finally ... and SQL2005 ... > > > > > > > > I should add that I have read about .NET but never really gotten down > to > > > > writing code using Visual Studio and the .net platform - > > > > > > > > regards, > > > > borge > > > > _______________________________________________ > > > > dba-SQLServer mailing list > > > > dba-SQLServer at databaseadvisors.com > > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > > > http://www.databaseadvisors.com > > > > > > > > > > > > > _______________________________________________ > > > dba-SQLServer mailing list > > > dba-SQLServer at databaseadvisors.com > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > > http://www.databaseadvisors.com > > > > > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >