Tuesday, August 30, 2011

Executing system stored procedures with a lower privileged user in SQL Server

We have a workflow that does a significant amount of data modification. Later during the day a second process will read the db where much of the data has change. However the issue we found was sql server was not updating it's statistics, and causing poor plans during the reads. Once stats were updated, plans returned to normal. The solution was to have our java client update stats using sp_updatestats before it started the reads. However since the user is fairly restricted and system sprocs such as this require elevated privileges, we would get the following error.
"Msg 15247, Level 16, State 1, Procedure sp_updatestats, Line 15
User does not have permission to perform this action."

To get around this we encapsulated this call within a user defined stored procedure, leveraging the Execute as option and executing as dbo. The example would look like this.

create procedure updatestatistics
with execute as 'dbo'
as

No comments: