Skip to main content

Running sp_updatestats on AWS RDS database

Image
By
Part of the maintenance tasks that I perform on a MSSQL Content Manager database is to run stored procedure sp_updatestats.

exec sp_updatestats

However, that is not supported on an AWS RDS instance. The error message below indicates that only the sa account can perform this:

Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15 [Batch Start Line 0]
User does not have permission to perform this action.

Instead there are several posts that suggest using UPDATE STATISTICS instead: https://dba.stackexchange.com/questions/145982/sp-updatestats-vs-update-statistics

I stumbled upon the following post from 2008 (!!!), https://social.msdn.microsoft.com/Forums/sqlserver/en-US/186e3db0-fe37-4c31-b017-8e7c24d19697/spupdatestats-fails-to-run-with-permission-error-under-dbopriveleged-user, which describes a way to wrap the call to sp_updatestats and execute it under a different user:

create procedure dbo.sp_updstats
with execute as 'dbo'
as
exec sp_updatestats
go


grant execute on dbo.sp_updstats to [TCMDBUser]
go

exec dbo.sp_updstats

I ran the code above as the RDS admin user and it seemed to work. The output was very similar to what the sp_updatestats would output, so I would conclude it actually worked ;)


Labels:

Comments

Unknown said…
This does not specify what database this stored procedure is being created in. What database? In RDS you cannot create a stored procedure in the master db
17 June, 2019 21:17
Post a Comment

Popular posts from this blog

I Have Gone Dark

By Unknown
Maybe it's the Holidays, but my mood has gone pretty dark. That is, regarding the look and feel of my computer and Tridion CME, of course. What I did was to dim the lights on the operating system, so I installed Placebo themes for Windows 7 . I went for the Ashtray look -- great name :) My VM looks now like this: But, once you change the theme on Windows, you should 'match' the theme of your applications. Some skin easily, some not. The Office suite has an in-built scheme, which can be set to Black , but it doesn't actually dim the ribbon tool bars -- it looks quite weird. Yahoo Messenger is skinnable, but you can't change the big white panels where you actually 'chat'. Skype is not skinnable at all. For Chrome, there are plenty of grey themes. Now i'm using Pro Grey . But then I got into changing the theme of websites. While very few offer skinnable interfaces (as GMail does), I had to find a way to darken the websites... Enter Stylish -- a pl...
4 comments
Read more

REL Standard Tag Library

By
The RSTL is a library of REL tags providing standard functionality such as iterating collections, conditionals, imports, assignments, XML XSLT transformations, formatting dates, etc. RSTL distributable is available on my Google Code page under  REL Standard Tag Library . Always use the latest JAR . This post describes each RSTL tag in the library explaining its functionality, attributes and providing examples. For understanding the way expressions are evaluated, please read my post about the  Expression Language used by REL Standard Tag Library . <c:choose> / <c:when> / <c:otherwise> Syntax:     <c:choose>         <c:when test="expr1">             Do something         </c:when>         <c:when test="expr2">             Do something else         </c:when...
2 comments
Read more