mardi 14 mai 2019

How does impersonation and security permissions in tSQLt tests work?

I have a tSQLt test which I expect to fail but it runs with success. When I run the code outside the procedure, it fails as expected, but when executing the test with Run, no error occurs.

I have read the question tSQLt Testing SQL Server security permissions but the accepted answer does not solve my problem.

My test looks like this:

    CREATE PROCEDURE TestSecurity.[test AFDK_Reader cannot read from AWS schema]
    AS 
    BEGIN
        --EXEC tSQLt.ExpectException
        EXECUTE AS USER = 'AFDK_Reader'

        select *
        from sys.user_token

        SELECT * FROM fn_my_permissions('AWS', 'SCHEMA')   
        ORDER BY subentity_name, permission_name ;   

        SELECT *
        FROM [AWS].[ADRESSEPUNKT_HISTORIK]
        REVERT
    END

The role has granted select permissions on the AFDK schema only and that is the only database role membership the SQL user has.

The AFDK_Reader has no permissions to read from the AWS schema.

Can anybody tell me how to get on with my debugging? Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire