12

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?

1

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 
| improve this answer | |
  • Sadly no, Amazon RDS doesn't give you access to the sysadmin role, nor does it allow you to alter the db_owner role for TempDB. – Brent Ozar May 16 at 10:40
  • Then the answer to your question is "No, there isn't a workaround for AWS RDS SQL Server so that they work." – stacylaray May 19 at 2:25
  • I appreciate your confidence, but I'm going to hold out hope that someone else can find a workaround. ;-) – Brent Ozar May 20 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.