Did some research in
SSISDB and found a user
AllSchemaOwner which is not associated with any SQL Server login:
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;
However, there are plenty of objects in the database, which were created with credentials of that account.
I've tried to run one of these procedures and it was executed successfully.
When I've tried to recreate that scenario in my test database it returned me an error:
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.
So, the question is: How it is possible that procedure created with an option
WITH EXECUTE AS 'AllSchemaOwner' can be successfully executed while SQL Server login does not exist for that user?