How to grant UPDATE STATISTICS to any table to a user

Question

According to SQL Server documentation, to run UPDATE STATISTICS ON a table you need the ALTER TABLE permission. I would like my user to have the permission to update stats on any table (current and future). I granted it the database permission to create tables (GRANT CREATE TABLE TO my_user) as it implies ALTER permission, but when I try to update stats on any table with that user, it doesn't work, I get that permission error :

Msg 1088, Level 16, State 12, Line 7
Cannot find the object "dbo.my_table" because it does not exist or you do not have permissions.

I know I can do GRANT ALTER ON dbo.my_table TO my_user but I'm searching for a solution that will allow it to any table (and added tables)

Answer 1

You could create a stored procedure to do the work… but it gets overly complex pretty fast. Here’s an outline of the whats and whys of how I’d do this:

• Create a stored procedure
• The user will need to provide the table to be updated, so the procedure will require a parameter
• All users whom you wish to be able to update the table will require EXECUTE privileges on the procedure
• The procedure will need to issue an ALTER TABLE to any given table in the database
• This will require building and executing a dynamic SQL statement within the procedure
• Debugging procedures with dynamic SQL is a pain, so add and account for an @Debug parameter
• The procedure will run with the rights of the user executing it, who will not have ALTER TABLE rights
• So, you have to create the procedure with the EXECUTE AS clause, which allows the code within the stored procedure to be run with privileges other than those of the user executing it
• I don’t recall, but I think you cannot use “EXECUTE AS dbo”.
• What I have done is, in the target database, create a “user without login”, grant it db_owner rights, and use that as in the EXECUTE AS statement.

Here’s some code that should do all this:

--  Set up the "user without login"
IF user_id('ExecuteDynamic') is null
 BEGIN
    CREATE USER ExecuteDynamic WITHOUT LOGIN
    EXECUTE sp_addRoleMember 'db_owner', 'ExecuteDynamic'
 END
GO


--  Used to drop and recreate the procedure on the fly
IF objectproperty(object_id('dbo.UserUpdatesTableStatistics'), 'isProcedure') = 1
    DROP PROCEDURE dbo.UserUpdatesTableStatistics
GO


--  Create the procedure
CREATE PROCEDURE dbo.UserUpdatesTableStatistics

    @TableName  sysname
   ,@Debug      tinyint  = 0
       --  Debug control:
       --    0 = Do the work
       --    1 = Do the work and show selected debugging information
       --    2 = Same as 1, but show and do *NOT* execute any dynamic code

WITH EXECUTE AS 'ExecuteDynamic'
AS

    SET NOCOUNT on

    DECLARE @Command nvarchar(200)

    SET @Debug = case when @Debug between 0 and 2 then @Debug else 0 end

    IF object_id(@TableName) is null
        --  If no such table, do nothing (this should catch SQL injection attacks)
        RAISERROR('Cannot update statistics, Table "%s" not found', 11, 1, @TableName)

    ELSE
     BEGIN

        --  Table exists, proceed
        SET @Command = 'UPDATE STATISTICS ' + @TableName

        IF @Debug > 0
         BEGIN
            PRINT '--  Dynamic SQL  ---------------------'
            PRINT @Command
            PRINT '--------------------------------------'
         END

        IF @Debug < 2
            EXECUTE sp_executeSQL @Command

     END

RETURN 0
GO

Note that this does not deal with schemas--it just assumes everything is in dbo. I might have missed another detail or two, but it should be enough to get you going.

Edit: Yep, there were overlooked details, as discussed in the comments.