sql-server - 如何让非系统管理员帐户执行 "DBCC CHECKIDENT"？

Question

在每次 DAO 测试之前，我都会清理我的数据库，并且我需要重置一些表的标识值。我创建了以下存储过程:

CREATE PROCEDURE SET_IDENTITY
    @pTableName           varchar(120),
    @pSeedValue           int
AS
BEGIN
    DBCC CHECKIDENT(@pTableName, RESEED, @pSeedValue);
END

我的问题是我需要用“普通”用户调用这个存储过程。为了正常工作，此用户不能是以下成员:sysadmin、db_owner、db_ddladmin。

我试过:

a) 创建过程以所有者身份执行

b) EXECUTE AS USER = 'sa' 在调用 DBCC CHECKIDENT 之前

但在这两种情况下我都回来了:

服务器主体 sa 在当前安全上下文下无法访问数据库 my_db_name。

我使用的是 Microsoft SQL Server Express(64 位)11.0.2100.60

提前谢谢你，

阿贝尔

Answer 1

这很容易做到，但一般来说，您不应该需要每次都重置标识值。具体的标识值应该无关紧要，因此唯一的问题应该是由于重复测试而可能达到最大值。在那种情况下，我不建议每次都重置，因为这也是让 ID 达到高值的一个很好的测试，这样您就可以确保所有代码路径都能正确处理它们，并在用户之前找到不处理的区域；-) .

也就是说，您需要做的(假设这是本地化到单个数据库)是创建一个非对称 key ，然后从中创建一个用户，然后将该用户添加到固定的 db_ddladmin数据库角色，最后使用相同的非对称 key 签署您的存储过程。

以下示例说明了此行为:

USE [tempdb];

CREATE TABLE dbo.CheckIdent
(
  [ID] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PK_CheckIdentity] PRIMARY KEY,
  [Something] VARCHAR(50)
);

EXEC(N'
CREATE PROCEDURE dbo.SET_IDENTITY
    @pTableName           sysname,
    @pSeedValue           int
AS
BEGIN
    DBCC CHECKIDENT(@pTableName, RESEED, @pSeedValue);
END;
');

CREATE USER [MrNobody] WITHOUT LOGIN;

GRANT EXECUTE ON dbo.SET_IDENTITY TO [MrNobody];

-------

EXECUTE AS USER = N'MrNobody';
SELECT SESSION_USER AS [CurrentUser], ORIGINAL_LOGIN() AS [OriginalLogin];

EXEC dbo.SET_IDENTITY N'dbo.CheckIdent', 12;
/*
Msg 2557, Level 14, State 5, Procedure SET_IDENTITY, Line 7 [Batch Start Line 30]
User 'MrNobody' does not have permission to run DBCC CHECKIDENT for object 'CheckIdent'.
*/

REVERT;
SELECT SESSION_USER AS [CurrentUser], ORIGINAL_LOGIN() AS [OriginalLogin];

-------

CREATE ASYMMETRIC KEY [DdlAdminPermissionsKey]
  WITH ALGORITHM = RSA_2048
  ENCRYPTION BY PASSWORD = 'not_a_good_password';

CREATE USER [DdlAdminPermissions]
  FROM ASYMMETRIC KEY [DdlAdminPermissionsKey];

ALTER ROLE [db_ddladmin] ADD MEMBER [DdlAdminPermissions];

ADD SIGNATURE
  TO dbo.SET_IDENTITY
  BY ASYMMETRIC KEY [DdlAdminPermissionsKey]
  WITH PASSWORD = 'not_a_good_password';

-------

EXECUTE AS USER = N'MrNobody';
SELECT SESSION_USER AS [CurrentUser], ORIGINAL_LOGIN() AS [OriginalLogin];

EXEC dbo.SET_IDENTITY N'dbo.CheckIdent', 12;
-- Success!

REVERT;
SELECT SESSION_USER AS [CurrentUser], ORIGINAL_LOGIN() AS [OriginalLogin];

其他小说明:

1. 您不能以 User = sa 身份执行，因为 sa 是登录(服务器级别)而不是用户(数据库级别)。您可以使用 EXECUTE AS LOGIN = 'sa'; 但这需要 IMPERSONATE 权限并且是一个安全漏洞，因为非特权登录可以运行 EXECUTE AS LOGIN = 'sa' 随时随地。所以不要那样做。
2. 对于包含 SQL Server 对象名称、索引等的变量/参数，您应该使用 NVARCHAR 而不是 VARCHAR。大多数内部名称使用 sysname，它是 NVARCHAR(128) 的系统别名，因此 sysname 通常是首选数据类型。