running sp_updatestats is producing problems.

Hi to all,

1.I have first a question about one SP sp_updatestats.

Because the SP it is asking in the implementation about the sysadmin rights i got problems at running time.

Our user it is the db-owner but it is no sysadmin.

To give an example the sp : sp_createstats it is not asking for this rights, and this function it creates new objects in the db.

My first question is Why the sp_updatestats must ask for this rights what is the oficcial ansqear from Microsoft about this ?

I make a note this problem persist in the SQL Server 2005.

2.Now because of this problem i have tried to copy the sp from SQL Server 2000 and 2005 and to modify the SP to ask only for the owner rights.

So instead of

if not is_srvrolemember('sysadmin') = 1

I ask only for: if not IS_MEMBER ('db_owner') = 1.

In sql Server 2000 everything works fine in the SQL Server 2005 I got a very strange error:

'stats_ver_current' ist not identified as a known Function(translated error!).

Very strange I try to create this SP-copy using the sa account.

I have search for the function 'stats_ver_current' in SQL server books online and I found no documentation about. Google was no help too.

I was looking in the sysobjects too, no success.

What I am doing hier wrong ?

What should be a good solution for my rights problem with sp_updatestats ?

I'd first like to gain an understanding as to why you are trying to update statistics in the way that you are doing. Have you proven that out-of-date statistics are causing a performance problem after performing a particular action, or are you updating the statistics just to be sure?

If you absolutely must update statistics in this way then in SQL Server 2005 you can use EXECUTE AS to change execution context to another login or database user - this feature could help you out.

There's more detail here:

http://www.databasejournal.com/features/mssql/article.php/3596701

Chris

|||

The reason because I need to run the SP it is because I saw that some statisctics after some operations are out of date.

Of course this problem occurres not always it can depends of a lot of factors.

The idea is I need to update them. So in the SQL Server 2005 I wanted to use the new SP because as Microsoft says in the documentation it is something new to the SQL Server 2005. In the new implementation are not any more all the statistics reupdated just only that ones that are not any more up to date. So of course this will help the performance and the SP will run faster doing just what it is necesary.

About your idea of using exec as I cannot use it.

I dont have any account that have the sysadmin rights.The client does not allow us to have such an account.

Lets say on the Server are more databases. On our database we are owners so we should be able to do all the staff we need.As a sysadmin we will have the full control of all the databases and this it is a problem for our clients of course.

As I sayd I can run the SP sp_createstats 'indexonly' ' without problems. But he SP sp_updatestats I cannot run it.

Any other ideas to my questions ?

|||

Check out the following link:

http://msdn2.microsoft.com/en-us/library/ms187348.aspx

The UPDATE STATISTICS command will allow you to update statistics for a single table and requires only ALTER permissions on that table - this is actually the command that sp_updatestats uses.

You could create your own version of the existing sp_updatestats stored proc, that exists in the master database in SQL Server 2005, and make use of the 'STATS_DATE' function rather than the stats_ver_current function. This isn't ideal but it is better than doing nothing and should help get around your problem. If you decide to go down this route and don't use a user with database owner permissions to execute the stored proc then you will need to add ALTER permissions onto each of the tables you wish to update.

Hope this helps,

Chris

|||

Thanks for your answear.

I know the function 'STATS_DATE' that I can use instead of stats_ver_current. It had this idea too.It doenst have the same performance as the function stats_ver_current, but I can use it.

Or I can say I will use the SQL Server 2000 Stored Procedure, who knows what other bugs are in the date statistics info.

So updating allways the statistics maybe it is not as bad as we can think.

I still wait an official answear from a Microsoft Employee(or who it is answearing for this forum) to my 2 questions:

Why the stored procedure sp_updatestats(SQL2k and SQL2k5) it is asking for the sysadmin rights, and why the stored procedure sp_createstats not ?

And why I cannot use the function stats_ver_current in SQL Server 2k5 ?

|||

There seems to be an error in Books OnLine regarding sp_updatestats. It says:

"Requires membership in the sysadmin fixed server role, or ownership of the database (dbo)."

However, using an account in db_owner, I get this error when I run it:

"User does not have permission to perform this action."

Yet I am able to use 'update statistics' on each of the tables -- including the sys.queue_message_x tables -- that sp_updatestats affects when I run it under a sysadmin account.

Thanks,

Ron Rice