Switching context with using EXECUTE AS ... not always switch it in fact as you wish.

  • Article

In this demo i will try to explain why SET TRUSTWORTHY ON on some databases may make the sysadmin job unsafe. In previous posts i explained how db owner (or any developer) can try to implemet simplest luring attack against server sysadmin. According to BOL to avoid this problem sysadmin should switch to the context of the account/login with lowest possible privileges. Lets try to test this approach.

USE master
GO

-- create test database
CREATE DATABASE [TestDB] ON PRIMARY ( NAME = N'TestDB', FILENAME = N'C:\TestDB.mdf', SIZE = 3072KB, FILEGROWTH = 1024KB )
 LOG ON ( NAME = N'TestDB_log', FILENAME = N'C:\TestDB_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%)
GO

-- set option
EXEC dbo.sp_dbcmptlevel @dbname=N'TestDB', @new_cmptlevel=90
GO

-- first time test with TRUSTWORTHY = OFF

ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
GO

-- create login Test
USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [TestDB]
GO

-- create user test
CREATE USER [Test] FOR LOGIN [Test]
GO

-- create test DDL trigger - fired on any DENY event
CREATE TRIGGER [ddl_test_trigger]
ON DATABASE
FOR DENY_DATABASE
AS
 
SET NOCOUNT ON

 IF IS_SRVROLEMEMBER ('sysadmin') = 1
  PRINT 'Sysadmin found, security can be broken easily.'
 ELSE
  PRINT 'Sysadmin not found, you are in safe.'
 
GO

-- test under sysadmin (sa) account
DECLARE @cmd nvarchar(1000)

SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd)
GO

-- you'll see "Sysadmin found, security can be broken easily."

-- so the next step according to BOL - switch to lowest privileges context for desired operation
DECLARE @cmd nvarchar(1000)
SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd) AS USER = 'dbo'
GO

-- you'll see now "Sysadmin not found, you are in safe."
-- Thats is our desired goal - to be in safe. Lets try to set TRUSTWORTHY ON

ALTER DATABASE [TestDB] SET TRUSTWORTHY ON
GO

-- try again the "safe" version
DECLARE @cmd nvarchar(1000)
SET @cmd = 'DENY BACKUP DATABASE TO [Test]'
EXEC (@cmd) AS USER = 'dbo'
GO

-- you'll see again "Sysadmin found, security can be broken easily."

-- clear objects
USE Master
GO

DROP DATABASE [TestDB]
GO

DROP LOGIN [Test]
GO

--------------------------------------------------------

As as result of this demo i would recommend you do not use TRUSTWORTHY ON on your databases. Otherwise before making any
changes under 'sa' account in unknown databases please check TRUSTWORTHY setting for the database you are
working with, because simple EXECUTE AS .... (even with cookie) will not protect you from loosing you privileges or
GRANT 'sa' privileges to everybody (as explained in my previous articles). And be always in safe :-).

--------------------------------------------------------