为 SQL Server 中的存储过程授予特殊权限

这很容易实现（包括处理多个数据库和动态 SQL），无需任何模拟（IMPERSONATE许可）、跨数据库所有权链接（服务器/实例或数据库设置）或TRUSTWORTHY（数据库设置）。一般来说，你需要做的是：

1. 在数据库 A 中创建证书
2. 使用ADD SIGNATURE对数据库 A 中的存储过程进行签名
3. 在数据库 B 中创建相同的证书
4. 根据该证书在数据库 B 中创建用户
5. 为新用户分配访问所需表所需的权限

例子：

清理

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

设置

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';


USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.RunReport
AS
SET NOCOUNT ON;

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
GO

GRANT EXECUTE ON dbo.RunReport TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[RunReport]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
                 AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

CREATE TABLE dbo.[RestrictedTable]
(
  [ID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_RestrictedTable] PRIMARY KEY,
  [Other] VARCHAR(50)
);

GRANT SELECT ON [dbo].[RestrictedTable] TO [PermissionsUser];

INSERT INTO dbo.[RestrictedTable] ([Other]) VALUES ('Ta da!');
GO

测试

-- Quick test to show that [PermissionsUser] cannot be Impersonated:
USE [DatabaseB];
EXECUTE AS USER = 'PermissionsUser';
/*
Msg 15517, Level 16, State 1, Line 123
Cannot execute as the database principal because the principal "PermissionsUser" does not
exist, this type of principal cannot be impersonated, or you do not have permission.
*/


-- Main test:
USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
/*
Msg 916, Level 14, State 1, Line 144
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB"
under the current security context.
*/

EXEC [dbo].[RunReport]; -- SUCCESS!!!
-- 1    Ta da!


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

有关模拟与模块签名的更多信息，您可以查看我的其他答案（以及其中链接的答案）：

SQL Server 模拟不工作

在服务器上创建一个用户帐户。在您希望他们具有有限访问权限的数据库中创建没有权限的用户。他们目前无权访问该数据库中的任何内容。

使用所需的有限列和权限创建所需表的视图。然后配对该用户以访问视图。

现在在您希望他们使用的数据库中，您可以在那里创建一个过程，该过程引用您想要的视图，同时限制他们的权限并且不允许他们访问基础表，以便他们现在可以进行更高级别的数据聚合。

这确实违反了无法添加他们对您的问题的请求，但这是一个很好的解决方法，同时让他们除了那个视图之外没有对该数据库中任何内容的实际访问权限。正如您的问题所述，他们不需要访问表，而是访问数据库。您将看到的错误实际上是：

消息 916，级别 14，状态 1，过程名称，第 11 行服务器主体“USER”无法在当前安全上下文中访问数据库“名称”

这个想法能解决问题吗？下面的例子。

CREATE DATABASE TestA; 
GO

CREATE DATABASE TestB;
GO

USE [master];
GO

CREATE LOGIN TestUser WITH PASSWORD = 'Password', CHECK_POLICY = OFF;
GO

CREATE TABLE TestA.dbo.t1(id INT);
GO

USE TestA; 
go 

CREATE VIEW dbo.ViewA
AS
  SELECT id FROM dbo.t1;
GO

CREATE USER TestUser FROM LOGIN TestUser;
GO

GRANT SELECT ON TestA.dbo.ViewA TO TestUser;
GO

USE TestB;
GO

CREATE USER TestUser FROM LOGIN TestUser;
GO

CREATE PROCEDURE dbo.TestProcedure 
AS
  SELECT id FROM TestA.dbo.ViewA;
GO

GRANT EXECUTE ON TestB.dbo.TestProcedure TO TestUser;
GO

基本上，您需要在安全数据库之外创建一个进程，以允许非授权用户以非常有限的方式查看数据。执行此操作的最简单方法是创建一个带有 EXECUTE AS 子句的存储过程，为过程中的代码提供公开所选数据所需的权限，但除此之外保持权限锁定。这看起来像：

Use AccessableDB
GO
CREATE PROCEDURE GetTheStuff (Param1 int, Param2 bit = 1)
WITH EXECUTE AS 'HighPermissionsUserInOtherDB'
AS
BEGIN 
    -- Code referencing InaccessableDB
END