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)

You could use [`sp_updatestats`](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-updatestats-transact-sql) inside a stored procedure and grant permissions to your stored procedure. That would prevent users from doing anything untoward. — HABO, Apr 24 '17 at 14:28
@HABO That's a good idea, but `sp_updatestats` runs against all tables, I cannot opt to choose the tables I want and the tables I don't want to have their stats updated. — MaxiWheat, Apr 24 '17 at 14:33
You can use the `update statistics` statement to update specific tables within a stored procedure. (Yeah, I was too lazy to check earlier.) Since your question mentioned "and added tables" I assumed ... . Even so, with dynamic SQL an SP could hunt down tables according to your rules and run `update statistics` on them. — HABO, Apr 24 '17 at 14:53

Philip Kelley · Accepted Answer · 2017-04-24T21:09:22.317

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.

Pretty neat! This is the kind of answer I was hoping for, thank you @Philip Kelley — MaxiWheat, Apr 24 '17 at 15:08
Stored procedures can run with their own permissions. ([Ref](https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/grant-permissions-on-a-stored-procedure).) If you want anyone to be able to execute `UpdateStatisticsForAccounting` then they can do so without needing _any_ user access to the tables.: — HABO, Apr 24 '17 at 17:05
It would be more reliable to use `SET @Command = 'UPDATE STATISTICS ' + QuoteName( @TableName );` to handle funny table names, e.g. `My Data`. And validating `@TableName` against [`sys.tables`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql) to ensure that it is a user table is probably a good idea. — HABO, Apr 24 '17 at 19:58
It's the dynamic SQL that causes the access privileges problem. The code/script within the procedure execute for any user with EXECUTE privileges on the procedure, but scripts dynamically built and execute within the procedure itself will, um, "revert" back to the access privileges of the entity that called it (because the code being executed was not parsed and compiled as part of the procedure itself). — Philip Kelley, Apr 24 '17 at 21:05
Definitely true, regarding quotename. I couldn't remember it, because I have control and never ever allow bogus names into my databases. — Philip Kelley, Apr 24 '17 at 21:07
And, yes, I was lazy again. Rather than sys.tables, I recommend the full `IF object_id(@TableName, 'U') is null` syntax. — Philip Kelley, Apr 24 '17 at 21:08

score -3 · Answer 2 · answered Apr 24 '17 at 14:13

-3

Try this

          EXEC sp_addrolemember N'db_datawriter',N'yourusername'

This will grant your user all permissions on all tables.

answered Apr 24 '17 at 14:13

Simran

102
1
8

3

That will also allow users to insert, update, and delete data in that table, which might not be acceptable...? – Philip Kelley Apr 24 '17 at 14:16
1

It just don't work, I get the same permission error when using that role – MaxiWheat Apr 24 '17 at 14:18
Update stats requires ALTER TABLE permissions, whereas the datawriter role allows you to change data only, not alter the table. – blobbles Jun 25 '21 at 07:29

How to grant UPDATE STATISTICS to any table to a user

2 Answers2