将sysadmin角色与EXECUTE作为

Question

据我的理解，我可以使用EXECUTE AS OWNER子句作为我创建的过程的一部分，以使该过程的主体作为不同的用户运行。我的目标是执行一个需要sysadmin角色(DBCC TRACEON(1224))的命令。此过程应由非特权用户调用。

我在sa用户下运行了以下脚本：

SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin')
-- dbo  1   1

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'MyProc')
    DROP PROCEDURE MyProc

GO
CREATE PROCEDURE MyProc
WITH EXECUTE AS OWNER
AS 
    SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin');
-- dbo  1   0

    DBCC TRACEON(1224)
--Msg 2571, Level 14, State 3, Procedure MyProc, Line 7
--User 'dbo' does not have permission to run DBCC TRACEON.

RETURN
GO

EXEC MyProc

输出在注释中是内联的。事实证明，在过程之外，我似乎拥有sysadmin成员资格，而不是在过程中。

该过程由dbo用户拥有。我知道不可能将sysadmin角色授予数据库用户(至少GUI不提供这种可能性)。因此，我不知道如何才能使数据库用户具有服务器角色。

我还尝试了EXECUTE AS 'sa'，这导致了Cannot execute as the user 'sa', because it does not exist or you do not have permission.。文档声明我只能指定用户名，而不能指定登录名。所以我明白为什么那不管用。

如何使用sysadmin角色成员身份运行我的过程？

Answer 1

这是可以做到的，但一般认为是相当危险的。在非常基本的级别上，您可以在数据库上设置trustworthy标志，然后当在sp上使用execute as时，它可以利用它的服务器级主体安全访问。

因为这有多危险，我不想在这里谈任何细节。不过，我已经在博客上写了一些关于这里的文章，并给出了关于如何做的具体说明。

所有这些都要确保你绝对是NEED这样做的。你在打开一个很大的安全洞。如果您确实决定这样做，那么将您的sp放在它自己的数据库中，并且只允许用户连接和执行对SP的访问。

Answer 2

我的目标是执行一个需要sysadmin角色的命令(DBCC (1224))

通过允许非特权用户作为sysadmin角色运行，您的安全性出现了漏洞。

如果您试图设置基于锁数的1224跟踪滞后，这将禁用锁升级，则可以使用ALTER TABLE在表级别执行此操作。

例如，下面启用了已分区表上的锁升级到分区级别。如果未对表进行分区，则在表级别设置锁升级。

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO); --有效的选项有自动、表和禁用。

现在，您只需赋予一个绝对先进的用户alter权限即可。

HTH

Answer 3

创建一个SQL Server代理作业(由sysadmin成员拥有)将起到这个作用，尽管我意识到这不是一个非常好的解决方案。

用户可以使用msdb.dbo.sp_start_job (不同步)启动作业。但是，如果需要同步运行代理作业，则需要再执行几行代码。而且，很明显，您需要启动并运行Server代理服务。