Sql-server – UPDATE STATISTICS options fail in Amazon RDS SQL Server

amazon-rdssql server

In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t;
GO
CREATE TABLE #t (id INT NOT NULL);
GO
UPDATE STATISTICS #t 
GO

But try to specify the rowcount on the statistics:

UPDATE STATISTICS #t 
  WITH ROWCOUNT = 100000000;
GO

And you get an error:

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

The STATS_STREAM, ROWCOUNT, and PAGECOUNT options are documented, but not officially supported. Is there a workaround for AWS RDS SQL Server so that they work?

Best Answer

It is true that either sysadmin OR db_owner privileges are required to use those options, per SQL code. We know we do not have sysadmin privileges. For internal tables, sysadmin permissions would be required. Since temp tables are not internal, this does not apply. So that leaves one possibility - we must have db_owner privileges. Do we? In the SQL Code, it is being evaluated not at the current DB context, but of the database of the object we are attempting to alter: TempDB.

Now that we know why this fails, we can evaluate possible workarounds. 1. If Amazon RDS grants privileges to modify the tempdb database roles, adding the user to db_owner would do the trick. I haven't tested this, but based on this article believe it is likely doable. It would be the easiest and perhaps most elegant solution. 2. Populate the temp table with junk that matches the stats profile you're targeting, update stats with NORECOMPUTE, then truncate and populate/use it in the way originally intended.

PROOF: Login with sysadmin; verify

SELECT SUSER_SNAME() [CurrentLogin], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()) GO

Create test database and use it

CREATE DATABASE [Test] GO USE [Test] GO

Confirm that this issue doesn't happen with sysadmin

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t; GO CREATE TABLE #t (id INT NOT NULL; GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Create a user that doesn't have sysadmin privileges and use it

CREATE LOGIN [UpdateStatsLogin] WITH PASSWORD = 'DontPeek!' 
CREATE USER [UpdateStatsUsr] FROM LOGIN [UpdateStatsLogin]
SETUSER 'UpdateStatsUsr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME())

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Demonstrate that db_owner of the current database doesn't work

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

Repro the error

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Now add to db_owner in tempdb switch back to sysadmin & verify

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO USE TEMPDB GO CREATE USER [updatestatsusr] FROM LOGIN [updatestatslogin] GO ALTER ROLE [db_owner] ADD MEMBER [updatestatsusr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO

And watch the error disappear

USE [Test] GO SETUSER 'updatestatsusr' GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO

Remove from db_owner in Test to show that it's tempdb that matters

SETUSER GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO ALTER ROLE [db_owner] DROP MEMBER [UpdateStatsUsr] GO SELECT SUSER_SNAME() [CurrentLogin], USER_NAME() [CurrentUser], IS_SRVROLEMEMBER('SYSADMIN', SUSER_SNAME()), IS_ROLEMEMBER('db_owner', USER_NAME()) GO IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t GO CREATE TABLE #t (id INT NOT NULL); GO UPDATE STATISTICS #t WITH ROWCOUNT = 100000000; GO
Related Question