What permissions are required for UPDATE STATISTICS #table WITH ROWCOUNT = xxx?

By Posted on

Question :

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.

Answer :

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:

  1. create the stored procedure
  2. 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

  1. Create the certificate
  2. Sign the stored procedure with that certificate
  3. Copy certificate (public key only) to [master]
  4. 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):

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

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

    OR:

  2. 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:

  1. 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”)
  2. 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

You have to run exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000') within tempdb database.

Basically, temp tables exist in tempdb, not in others.

Eh, seems I was in error. The answer is simple: the user lacks permissions in the tempdb database to perform this update statistics command

temp tables are created in the tempdb database, and permissions by default are limited.

There are four types of tables:

  1. Physical Tables
    • Tables that are permanent and hold final values. Ownership is always related to the database they are created in.
  2. @Variable Tables
    • Tempdb Tables that only exist in a batch statement and are cleaned up at each GO batch separator or at the end of the script
    • SQL Optimizer has no statistics and treats it as having on row.
  3. #Temp Tables
    • Tempdb Tables that persist for the entire session, not just a batch statement.
    • statistics are created and persist at creation time.
    • Don’t Alter a Temp table
    • Is cleaned up by the garbage collector at the end of session, so don’t drop temp tables
    • is always created in the dbo schema. Any reference even in create statement to another schema is ignored.
  4. ##Global Tables
    • the dual ## in the name (##temptable vs #temptable) lets you know its global
    • Tempdb Tables that are available to all user across the Instance.
    • can be modified by another process during your batch execution
    • Is cleaned up after all sessions that reference the table are closed.
    • is always created in the dbo schema. Any reference even in create statement to another schema is ignored.

Worth noting that Temp objects can be referenced via tempdb..#tablename, though explicit usage of the tempdb name in normal statements is ignored since all temporary tables are created in Tempdb and in the dbo schema

Notice how they all are in the tempdb database. The reason yourstatement is failing is simply due to the #temp table existing in the tempdb and you likely have not given the user permissions in the tempdb to perform the update.

Normally, all you need it to grant alter on the table, though I found that temp tables are a little different and might require higher permissions. but since this is the same session, just do this:

REVERT
UPDATE STATISTICS ON #TABLE
EXEC AS USER='User’

Now you don’t even need to grant elevated permissions to the user and satisfied your problem.

I did some additional digging and found the following:
Any query on the temp table works fine, its the WITH ROWCOUNT that causes the issue. A standard UPDATE STATISTICS also proceeds without issue.

I went through MSDN documentation, and I didnt find an explicit note to the SET ROWCOUNT permissions combined with UPDATE STATISTICS – after some speculation and testing I found that the documentation for DBCC UPDATEUSAGE lays out a need for sysadmin or db_owner on the database which contains the table.

If you add this to my example, the code works:

USE [tempdb]
GO
CREATE USER [testtwouser] FOR LOGIN [testtwouser]
GO
USE [tempdb]
GO
ALTER ROLE [db_owner] ADD MEMBER [testtwouser]
GO

Unfortunately my original code did not clarify that its being run in another database besides tempdb, but that’s the implicit assumption that if you ignore, has the entire script work (because they now have tempdb db_owner.)

Leave a Reply

Your email address will not be published. Required fields are marked *