Updating statistics with limited privileges user or store procedure

• sql-lover

  SSCoach

  Points: 18530

  More actions

  December 13, 2013 at 8:10 am

  #395983

  Hi,

  I am trying to update statistics calling a store procedure that resides on a admin database, located on the same instance. But for some reason, when it runs, can't find the table.

  Here's the code:

  ALTER PROCEDURE [dbo].[Man_UpdateStats]

  AS

  BEGIN

  DECLARE @name NVARCHAR(50)

  DECLARE @sql nvarchar(255);

  SET @name = DB_NAME()

  SET @sql = 'USE '+ @name+'; EXECUTE [ABC].[dbo].[UpdateStats_v3];';

  EXECUTE sp_executesql @sql

  END

  GO

  Below the actual store procedure that will update the statistics and resides on database ABC:

  ALTER PROCEDURE [dbo].[UpdateStats_v3]

  AS

  BEGIN

  UPDATE STATISTICS MyTable

  END

  I don't understand why even when using dynamic sql, the store procedure on ABC can't find the table. Any ideas?

  "MyTable" is on the source database, not on ABC.

  By the way, I know I can accomplish the same using a user on the source database with limited privileges and create a store procedure that does the same ;-), but I don't want another user on the client's table, I want to invoke the UPDATE STATISTICS command from another database.