我有这个设置
- 我创建了两个数据库（源和目标）。
- 源数据库具有正在访问目标数据库中的表的存储过程。
- 存储过程作为服务帐户执行
- 服务帐户登录在两个数据库中都有用户帐户
- 服务帐户具有目标数据库的连接和身份验证权限
- 服务帐户对目标数据库架构具有读取权限
- 两个数据库都值得信赖
我仍然有一个错误
服务器主体“ServiceAccount”无法在当前安全上下文下访问数据库“TargetDatabase”。
SQL 脚本
-- Create logins
CREATE LOGIN SourceDatabaseOwner WITH PASSWORD = 'Pa$$w0rd'
CREATE LOGIN TargetDatabaseOwner WITH PASSWORD = 'Pa$$w0rd'
CREATE LOGIN ServiceAccount WITH PASSWORD = 'Pa$$w0rd'
-- Create databases
CREATE DATABASE SourceDatabase
CREATE DATABASE TargetDatabase
-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY ON;
ALTER DATABASE TargetDatabase SET TRUSTWORTHY ON;
-- Setup database owners
USE SourceDatabase
GO
EXEC dbo.sp_changedbowner @loginame = N'SourceDatabaseOwner'
USE TargetDatabase
GO
EXEC dbo.sp_changedbowner @loginame = N'TargetDatabaseOwner'
-- Add ServiceAccount to source database
USE SourceDatabase
GO
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;
-- Add ServiceAccount to target database
USE TargetDatabase
GO
CREATE USER ServiceAccount FOR LOGIN ServiceAccount;
-- Enable ServiceAccount to authenticate to target database
USE TargetDatabase
GO
GRANT AUTHENTICATE TO ServiceAccount;
-- Grant permissions
USE TargetDatabase
GO
GRANT SELECT ON SCHEMA::dbo TO ServiceAccount
-- Create table with data
USE TargetDatabase
GO
create table dbo.InterestingData (Id int identity primary key, Content nvarchar(255))
insert into dbo.InterestingData (Content) values ('Foo'), ('Bar')
-- Create stored procedure executing under service account and accessing target database
USE SourceDatabase
GO
create or alter procedure dbo.GetData
with execute as 'ServiceAccount' as
begin
select id, content from TargetDatabase.dbo.InterestingData
end
-- Execution does not work under service account
-- The server principal "ServiceAccount" is not able to access the database "TargetDatabase" under the current security context.
exec dbo.GetData
所以我继续并尝试使用证书签署程序并在目标表上设置权限以登录该证书
-- create certificate in master
use master
go
CREATE CERTIFICATE SignProcedureCert
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
WITH SUBJECT = 'Certificate for signing stored procedures'
GO
-- backup certificate
BACKUP CERTIFICATE SignProcedureCert TO FILE ='C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY
(
FILE = 'C:\Certs\SignProcedureCert.pvk',
DECRYPTION BY PASSWORD = 'Pa$$w0rd',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
)
GO
-- create login from the certificate
create login SignProcedureLogin from certificate SignProcedureCert
-- Import certificate to the source database
CREATE CERTIFICATE SignProcedureCert
FROM FILE = 'C:\Certs\SignProcedureCert.cer'
WITH PRIVATE KEY (FILE = 'C:\Certs\SignProcedureCert.pvk',
ENCRYPTION BY PASSWORD = 'Pa$$w0rd',
DECRYPTION BY PASSWORD = 'Pa$$w0rd')
-- Sign the procedure
ADD SIGNATURE TO dbo.GetData
BY CERTIFICATE SignProcedureCert
WITH PASSWORD = 'Pa$$w0rd';
GO
-- Setup permission for SignProcedureLogin to target database
USE TargetDatabase
GO
CREATE USER SignProcedureLogin FOR LOGIN SignProcedureLogin;
GRANT SELECT ON SCHEMA::dbo TO SignProcedureLogin
所以现在我应该有这个设置
但是当我执行
USE SourceDatabase
GO
exec dbo.GetData
我得到了同样的错误
服务器主体“ServiceAccount”无法在当前安全上下文下访问数据库“TargetDatabase”。
我错过了什么？
更新
当我删除该with execute as条款时，它确实有效
-- Setup trustworthy
ALTER DATABASE SourceDatabase SET TRUSTWORTHY OFF;
ALTER DATABASE TargetDatabase SET TRUSTWORTHY OFF;
USE SourceDatabase
GO
create or alter procedure dbo.GetData
--with execute as 'ServiceAccount'
as
begin
select id, content from TargetDatabase.dbo.InterestingData
end
GRANT EXECUTE ON SCHEMA::dbo TO ServiceAccount
execute as login = 'ServiceAccount'
exec dbo.GetData
revert
但是仍然可以使用该with execute as子句以某种方式执行该过程吗？
