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

GRANT IMPERSONATE ON Login::L2 TO LC

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.
Correct?

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
go
grant exec on P to L;
go

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

.

Relevant Pages

  • Re: Running into a roadblock when using execute as and sys.databas
    ... GRANT IMPERSONATE ON Login::L2 TO LC ... You cannot grant IMPERSONATE on a login/user created from a certificate, ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Running into a roadblock when using execute as and sys.databas
    ... With the execute as clause, ... you use EXECUTE AS LOGIN in the body, and sign them with the certificate. ... CREATE LOGIN imperscert_login FROM CERTIFICATE imperscert ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and sch
    ... Whatever principal you impersonate with EXECUTE AS must have a security context in both databases but it doesn't need to be dbo. ... CREATE USER dispatcher ... The reason EXECUTE AS OWNER works with dbo as the schema owner is that 1) the certificate is a trusted authenticator because you granted AUTHENTICATE and 2) the impersonated dbo principal exists in dispatchDB by virtue of the fact that both databases are owed by the same server principal. ... You can still use the certificate as the authenticator as long as the impersonated principal has a security context in the referenced database as well. ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Cross-database execution permissions with certificates and schemas
    ... make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner', ... Discard the private key from the certificate, ... make the provider user the owner (EXEC sp_addrolemember N'db_owner', ... execute [dispatch] ...
    (microsoft.public.sqlserver.security)