Part of the maintenance tasks that I perform on a MSSQL Content Manager database is to run stored procedure 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:
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:
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 ;)
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 ;)
Comments