Updating statistics with limited privileges user or store procedure

  • 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.

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply