1

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?

1

Finally found an answer:
You can create database user, which won't have a SQL Login and won't be able to authenticate, but will have permissions and can be specified in WITH EXECUTE AS clause for stored procedures and functions.

The simple way to create such a user is just to use clause WITHOUT LOGIN during user creation:

Use SSISDB;
GO
CREATE USER 'AllSchemaOwner' WITHOUT LOGIN;
GO
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.