55a5439a   
   From: esquel@sommarskog.se   
      
   bill (billmaclean1@gmail.com) writes:   
   > I want database 'A' to be a "black box" that performs operations   
   > related to database 'B'.  'A' has some control data, and a number of   
   > stored procs, views, and table-valued functions that help the users of   
   > database 'B' do their jobs.  The objects in 'A' have to act   
   > (sometimes) on the tables in database 'B'.  There is a lot of logic   
   > and intellectual property in 'A' that I need to protect from prying   
   > eyes.   
      
   What you most of all need is a license agreement that controls what   
   users can do and not.   
      
   > I think that the WITH ENCRYPTION option is basically worthless for   
   > objects, because a number of tools can easily break that encryption.   
      
   That is true. However, it can be a supplement to a license agreement   
   and act as a sign saying "no trespassing".   
      
   Then again, using encryption means a lot of hassle for anyone who   
   needs to do performance tuning.   
      
   > 1.     I want the user of database 'B' to be able to SELECT from   
   > A.schema.ip_view and use it in his/her own queries with other tables   
   > in database 'B'.   
      
   As I mentioned this can be addressed with DB-chaining. However, this is   
   not on by default, and the DBA may not like enabling it.   
      
   > 3.     Ideally, a display of the plan would just show joins to   
   > A.schema.ip_view, not the constituent objects of that view.   
      
   Hm, there is a SHOWPLAN permission on database level. Really how this   
   works if a user that has this permission runs a crossdb query to a   
   database where he does not have this permission, I don't know.   
      
   But of course, this does not apply to admin users.   
      
   > 4.     Some stored procs in database 'A' will make/update tables and dump   
   > them into database 'B'.  (I could avoid this if it is a big problem,   
   > but it would be nice to do)   
      
   As long as DB-chaining is enabled, this should work.   
      
   > 5.     I don't want the user of database 'B' to know anything about the   
   > workings of view A.schema.ip_view.   To that user, A.schema.ip_view   
   > should just be a "black box" that returns data.  The user shouldn't be   
   > able to see the source A.schema.ip_view, nor object   dependencies,   
   > etc.   
      
   You need to revoke VIEW DEFINITION to the users. Although it would   
   probably be easier to put objects in B that invokes those in A.   
      
   > 6.     I don’t want to make it difficult to develop in 'A' for authorized   
   > developers.   
   > 7.     I want to be able to remotely connect to 'A' to create or alter   
   > objects as needed.   
      
   Just grant those people the necessary permissions.   
      
   > I thought that locking down access to database ‘A’ would be the best   
   > way, and I could just grant SELECT privileges on the appropriate   
   > objects in database ‘A’, but maybe there is a better or easier way to   
   > accomplish the goal.   
      
   Just keep in mind that you can never lock out people with admin rights.   
   Regular non-priv users you can keep out. Then again, it is not clear   
   why you have two databases in the first place. You can put your objects   
   in the same database, in different schemas. Then you grant users necessary   
   permissions in one schema only, and they can access the other objects   
   through objects in the first schema thanks to ownership chaining.   
      
   As for SHOWPLAN, the easist is to deny them this permission on schema   
   level.   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)