使用ROWCOUNT = xxx更新统计信息#表需要哪些权限？

Question

有人能向我指出除SA之外我需要的正确文档或附加权限来执行以下操作吗？

当我运行它时，我会收到以下错误：

找不到对象"#test“，因为它不存在，或者您没有权限。

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;

检查https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-updateusage-transact-sql?view=sql-server-2017中的文档时，db_owner或SA是可以的，但在我的测试中，db_owner甚至都不起作用。

Answer 1

您必须在tempdb数据库中运行exec('UPDATE STATISTICS #test WITH ROWCOUNT = 1000000')。

基本上，temp表存在于tempdb中，而不存在于其他表中。

Answer 2

呃，好像我错了。答案很简单:用户在tempdb数据库中缺乏执行此更新统计信息命令的权限。

临时表是在tempdb数据库中创建的，默认情况下权限是有限的。

有四种类型的表格：

1. 物理表
   • 永久的并保存最终值的表。所有权总是与在其中创建的数据库相关。

2. @可变表
   • 只存在于批处理语句中并在每个GO批处理分隔符或脚本末尾清理的Tempdb表。
   • SQL优化器没有统计信息，并将其视为有行。

3. #Temp表
   • 在整个会话中持久化的Tempdb表，而不仅仅是批处理语句。
   • 统计信息在创建时被创建并持续存在。
   • 不要更改临时表
   • 在会话结束时由垃圾收集器清理，因此不要删除临时表。
   • 总是在dbo架构中创建。即使在create语句中对另一个架构的任何引用都将被忽略。

4. ##Global表
   • 名称中的双重## (##temptable vs #temptable)让您知道它的全局性
   • 对整个实例的所有用户都可用的Tempdb表。
   • 可以在批处理执行期间由另一个进程修改。
   • 在关闭所有引用该表的会话后，将对其进行清理。
   • 总是在dbo架构中创建。即使在create语句中对另一个架构的任何引用都将被忽略。

值得注意的是，可以通过tempdb..#tablename引用Temp对象，但是忽略了在普通语句中显式使用Temp名称，因为所有临时表都是在Tempdb和dbo模式中创建的。

请注意它们都是如何存在于tempdb数据库中的。您的语句失败的原因很简单，是由于tempdb中存在的#temp表，而且您很可能没有在tempdb中授予用户执行更新的权限。

通常，您需要它在表上授予alter，尽管我发现临时表有点不同，可能需要更高的权限。但既然这是同一个会议，就这么做吧：

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

现在，您甚至不需要向用户授予提升的权限并满足您的问题。

Answer 3

我做了一些额外的挖掘，发现如下:对temp表的任何查询都很好，引起问题的是WITH ROWCOUNT。标准的更新统计数据也没有问题地进行。

我查看了MSDN文档，没有找到一个关于SET权限和更新统计信息的明确说明--经过一些猜测和测试后，我发现DBCC UPDATEUSAGE的文档在包含表的数据库中需要sysadmin或db_owner。

如果将此添加到我的示例中，则代码工作如下：

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

不幸的是，我的原始代码并没有说明它正在运行在除tempdb之外的另一个数据库中，但这是一个隐含的假设，即如果您忽略它，整个脚本就可以工作(因为它们现在有了db_owner)。