What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx? Can anyone point me to the correct documentation or additional permissions I need besides SA to do the following?

When I run it, I receive the following error:

Cannot find the object "#test" because it does not exist or you do not have permissions.

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.server_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE LOGIN [testlimiteduser] WITH PASSWORD=N'apassword', DEFAULT_DATABASE=[tempdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END

IF NOT EXISTS 
(
SELECT 
    name  
FROM sys.database_principals
WHERE 
    name = 'testlimiteduser'
)
BEGIN
    CREATE USER [testlimiteduser] FOR LOGIN [testlimiteduser] WITH DEFAULT_SCHEMA=[dbo]
END 

IF NOT EXISTS
(
    SELECT 1
    FROM sys.database_principals AS p
    WHERE 
        p.name like 'testlimiteduser_app'
)
BEGIN
    CREATE ROLE testlimiteduser_app
    GRANT EXECUTE TO testlimiteduser_app 
    ALTER ROLE [db_datareader] ADD MEMBER testlimiteduser_app
    ALTER ROLE [db_datawriter] ADD MEMBER testlimiteduser_app
    ALTER ROLE testlimiteduser_app ADD MEMBER [testlimiteduser]   
    ALTER ROLE db_owner ADD MEMBER testlimiteduser  --   https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA
END

GO

exec as user='testlimiteduser'

drop table if exists #test
create table #test (id int )
exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000') 
select * from #test 
revert;


Checking the documentation in https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017 says db_owner or SA are fine, but it doesnt even appear db_owner works in my testing.
Making an account db_owner, even for [tempdb], is still risky. Fortunately, it's not necessary to do this in order to accomplish the goal. You can simply use Module Signing to allow a stored procedure residing in [tempdb] make use of the db_owner database role:

Initial Setup

This just gets us to the same state as the code provided in the question (minus the testlimiteduser_app role, which is unnecessary for this goal).

IF (DB_ID(N'NotTempDB') IS NULL)
BEGIN
    CREATE DATABASE [NotTempDB];
    ALTER DATABASE [NotTempDB]
      SET RECOVERY SIMPLE;
END;
GO

USE [NotTempDB];

IF (SUSER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE LOGIN [testlimiteduser]
      WITH PASSWORD = N'apassword',
      DEFAULT_DATABASE = [tempdb],
      CHECK_EXPIRATION = OFF,
      CHECK_POLICY = OFF;
END;
GO

IF (USER_ID(N'testlimiteduser') IS NULL)
BEGIN
    CREATE USER [testlimiteduser]
      FOR LOGIN [testlimiteduser];
END;


Basic Setup

We need a module that can be signed (ad hoc SQL will not work), but we are not signing it just yet.

Here we:


create the stored procedure
grant the ability to execute that stored procedure to the test user.


GO
CREATE OR ALTER PROCEDURE dbo.[UpdateStats]
(
  @TableName sysname,
  @RowCount INT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = CONCAT(N'UPDATE STATISTICS ',
    QUOTENAME(@TableName),
    N' WITH ROWCOUNT = ',
    @RowCount);

EXEC (@SQL);
GO


GRANT EXECUTE ON dbo.[UpdateStats] TO [testlimiteduser];
GO


TEST 1

Please note: is best to use EXECUTE AS LOGIN, not AS USER as LOGIN is not only more accurate in terms of what happens when the app really does connect to SQL Server, but doing USER will add restrictions that might skew the behavior / test results.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;


Module Signing Setup


Create the certificate
Sign the stored procedure with that certificate
Copy certificate (public key only) to [master]
Create the certificate-based login from it.


IF (CERT_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE CERTIFICATE [Permissions$UpdateStats]
    ENCRYPTION BY PASSWORD = 'Super Bass-o-matic ''76'
    WITH SUBJECT = N'Permission to exec UPDATE STATISTICS ... WITH ROWCOUNT = ...',
    EXPIRY_DATE = '2099-12-31';
END;


-- Associate permissions (via future cert-based login) to stored procedure:
ADD SIGNATURE TO dbo.[UpdateStats]
  BY CERTIFICATE [Permissions$UpdateStats]
  WITH PASSWORD = 'Super Bass-o-matic ''76';


-- Copy certificate (public key only) to [master]
-- and create the cert-based login from it.
DECLARE @CopySQL NVARCHAR(MAX) = N'
USE [master];
    CREATE CERTIFICATE [Permissions$UpdateStats]
      FROM BINARY = ' + CONVERT(NVARCHAR(MAX),
      CERTENCODED(CERT_ID(N'Permissions$UpdateStats')), 1) + N';

    CREATE LOGIN [Permissions$UpdateStats]
      FROM CERTIFICATE [Permissions$UpdateStats];
';

EXEC(@CopySQL);


TEST 2

We signed the stored procedure and associated it with a login, but we haven't yet given that login the necessary permission, so the results are the same.

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- same error (1088: cannot find object "#test")
REVERT;


Final Setup: Assign Permission to Login OR User

This was kept as a separate step only to make it very clear that it is indeed the module signing that is getting this to work. And you have two options (pick only one):


add the existing certificate-based login to the sysadmin instance-level role  

ALTER SERVER ROLE [sysadmin]
  ADD MEMBER [Permissions$UpdateStats];


OR:
Create a User in tempdb from the certificate-based login, then add the new User to the db_owner database-level role

USE [tempdb];
IF (USER_ID(N'Permissions$UpdateStats') IS NULL)
BEGIN
    CREATE USER [Permissions$UpdateStats]
      FOR LOGIN [Permissions$UpdateStats];

    ALTER ROLE [db_owner]
      ADD MEMBER [Permissions$UpdateStats];
END;



TEST 3

USE [NotTempDB];
EXEC AS LOGIN = 'testlimiteduser';

DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id INT);
EXEC('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000');
-- expected error (1088: cannot find object "#test")
SELECT * FROM #test;

EXEC dbo.[UpdateStats] N'#test', 1000000;
-- SUCCESS!!!!

REVERT;


Conclusion

The main differences between the options are:


Option 1


Good: no object in tempdb (that needs to be created upon each start of the SQL Server service)
Bad: sysadmin permission is more powerful (not exactly "least privileged")

Option 2


Good: db_owner is more contained than sysadmin, in terms of power
Bad: object in tempdb (that needs to be created upon each start of the SQL Server service)



The difference between sysadmin and db_owner` is minimal, if not even relevant here, since the only thing that can be done with either of those permissions is what the code in the stored procedure does (this is one of the main benefits of module signing: it's highly granular).

So on a practical level, the complexity of creating the User in tempdb and adding it to db_owner upon each start of the SQL Server service (which requires creating a stored procedure in master, marking it as a "startup procedure", and enabling the instance-level config option "scan for startup procs") is worse than making the login sysadmin.

If the DB needing the additional permission was a user database, then I would probably go with Option 2, even if only for the purpose of having it match more closely with the intended use, as opposed to their being any real concern over sysadmin vs db_owner.



For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining