Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating statistics with limited privileges user or store procedure Expand / Collapse
Author
Message
Posted Friday, December 13, 2013 8:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 10:36 AM
Points: 384, Visits: 1,269
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.
Post #1522709
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse