做了一些研究SSISDB
,发现一个AllSchemaOwner
与任何 SQL Server 登录无关的用户:
SELECT p.name, p.type_desc
FROM SSISDB.sys.database_principals as p
LEFT JOIN sys.sql_logins as l ON p.sid = l.sid
WHERE p.principal_id > 4 and p.type = 'S' and l.sid is Null;
但是,数据库中有很多对象是使用该帐户的凭据创建的。
我已尝试运行其中一个程序并成功执行。
当我尝试在我的测试数据库中重新创建该场景时,它返回了一个错误:
USE [master]
GO
CREATE LOGIN [Test2] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [MyTestDB]
GO
CREATE USER [test3] FOR LOGIN [test2]
GO
SELECT 1 as test INTO tbl_Test;
GO
GRANT SELECT ON tbl_Test TO [test3]
GO
CREATE PROCEDURE sp_Select_TEST
WITH EXECUTE AS 'Test3'
AS SELECT * FROM tbl_Test;
GO
EXECUTE sp_Select_TEST;
GO
DROP LOGIN [test2];
GO
EXECUTE sp_Select_TEST;
GO
DROP PROCEDURE sp_Select_TEST
GO
DROP TABLE tbl_Test
GO
DROP USER [test3];
Msg 15517, Level 16, State 1, Procedure sp_Select_TEST, Line 0 [Batch Start Line 29]
Cannot execute as the database principal because the principal "test3" does not exist, this type of principal cannot be impersonated, or you do not have permission.
WITH EXECUTE AS 'AllSchemaOwner'
所以,问题是:当该用户不存在 SQL Server 登录时,如何成功执行使用选项创建的过程?