Using sp_MSforeachdb in SQL6.5

• sftey1

  SSC Veteran

  Points: 215

  More actions

  September 17, 2001 at 2:03 am

  #77894

  I was trying to use above mentioned stored procedure but hit the following error :

  Msg 229, Level 14, State 2

  SELECT permission denied on object #SQLOLEDbUserProfile00000000F6, database tempdb, owner dbo

  Any idea how to solve it ?

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  September 17, 2001 at 5:48 pm

  #418289

  My guess (and only a guess) is that you're trying to access a protected temp table. Maybe modify the proc to skip tables that start with a #? And/or skip tempdb altogether?

  Andy

  Andy
  Connect with me on LinkedIn

• sftey1

  SSC Veteran

  Points: 215

  More actions

  September 18, 2001 at 7:30 pm

  #418315

  The temp object was created once I connected to SQL server. If I choose to skip the temp table, sp_MSforeachdb will complain the object is not found.

  Any suggestion ?

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  September 19, 2001 at 4:35 am

  #418324

  I dont have 6.5 installed ANYWHERE. Let me see what I can come up with. Steve?

  Andy

  Andy
  Connect with me on LinkedIn