Query run by sa cannot see a View that is not owned by dbo

• Alex-815008

  Mr or Mrs. 500

  Points: 510

  More actions

  March 16, 2010 at 5:25 am

  #218002

  I have a View that is owned by an owner that I have created; it's name is:-

  [sales].[SalesPurchaseOrder]

  When I run a select statement having logged on as sa, I get this output:-

  select top 10 * from SalesPurchaseOrder

  Msg 208, Level 16, State 1, Line 1

  Invalid object name 'SalesPurchaseOrder'.

  However, if I modify my statement to include the owner then it works ok:-

  select top 10 * from sales.SalesPurchaseOrder

  -- << Returns a recordset >>

  So I think this is to with the fact that when the object's owner is not specified, SqlServer will look at the current owner first and then look at dbo if nothing is found.

  But I am already "dbo" - and I want SqlServer to browse through sales when it can't find the object in dbo. Is there a way of doing this?

  The reason why I want this particular behaviour is because we are changing the DB schema and during the transitional phase we want the existing SPs to be able to see the new objects without (ideally!) having to go and update all the SPs to add in sales.

• Alex-815008

  Mr or Mrs. 500

  Points: 510

  More actions

  March 22, 2010 at 7:44 am

  #1137595

  I've got this to work:-

  sp_changeObjectOwner 'dbo.GetSalesPurchaseOrder', 'sales'

  This changes the owner, and when the existing apps connect with that owner information everything works ok.