Stored Proc Ownership Chain Issue

• Steve Adams

  Mr or Mrs. 500

  Points: 520

  More actions

  October 17, 2005 at 9:02 am

  #152588

  I have a stored proc that creates a table and then fills it with data. Works fine for me because I have admin rights.  I took the dbo as ownername off the table when I create it because I want the table specific for the user that is executing the stored proc. However, when the stroed proc executes the code to INSERT data into the newly created table I get an invalid object error on the newly created table. The table is in the database with the owner as the user that created the table.  I am thinking that since the owner of the stored proc is dbo then the table that is trying to be accessed in the stored proc is dbo.tablename instead of username.tablename.  Since the users are always different is there a way to specify who the ownername of the able is without using dynamic sql?  In other words instead of the table created being a community bucket of data that user are grabbing from which can skew the data when different time periods are selected, I want them to create and fill thier own bucket to grab the data from.

  Thanks in advance.

• noeld

  SSC Guru

  Points: 96590

  More actions

  October 17, 2005 at 12:37 pm

  #597727

  Simply put:

  Use a permanent table and add a column with s_user_name as default. Then all queries should be filtered by s_user_sname (with a view perhaps  )

  Cheers!

   

  
  * Noel