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://learn.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://learn.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

StackExchange Database Administrators Q#239032, answer score: 4