做了一些研究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 登录时,如何成功执行使用选项创建的过程?
终于找到了答案:
您可以创建数据库用户,该用户没有 SQL 登录名,也无法进行身份验证,但有权限,可以在
WITH EXECUTE AS
存储过程和函数的子句中指定。创建此类用户的简单方法是
WITHOUT LOGIN
在用户创建期间使用子句: