Re: Running into a roadblock when using execute as and sys.databas


You cannot grant IMPERSONATE on a login/user created from a certificate,
since you cannot impersonate these principals anyway. The sole reason
you have these login/users is to grant them rights, as you cannot grant
rights directly to the certificate.

Ok, which means, generally speaking, that LC can impersonate L2, i.e. that
the following works:

execute as login = 'LC';
execute as login = 'L2';

However, if LC is created from a certificate, the first statement will fail.

Anyway, and you must have me execused, I have obviously not fully grasped
this. Before introducing the execute as clause, the P in our application was:

create proc P as
exec P1; -- owned by dbo using dynamic sql on tables that L cannot do
anything with and not executable by L
exec P2; -- owned by dbo and queries sys.databases, and not executable by L
grant exec on P to L;

As discussed above, this will not work neither as it is nor with the execute
as clause. If L cannot impersonate a login/user mapped to a certificate and
using the execute as statement within P, I still don't see how the solution
could be (assuming that TRUSTWORTHY is not used). (The login L and its
permissions to execute P are given and cannot be changed.)

Thanks for your patience!
Lars T
Siemens Healthcare, Norway