SSISDB user "AllSchemaOwner" is not SQL Server Login

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?

Replay

Category: sql server Time: 2016-07-31 Views: 5

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.137 (s). 12 q(s)