Admin: Elevate Privileges with TRUSTWORTHY

The database scoped TRUSTWORTHY flag was introduced in SQL 2005 and . Its only a guess, but I dare say that its often turned on as its the path of least resistance without understanding its consequences. For example, when deploying CLR assemblies which require EXTERNAL_ACCESS its far easier to just set the TRUSTWORTHY flag than create an asymetric key and login specifically for that routine - this is something i'm often guilty of. In many scenarios, its probably not an issue as the databases do not hold sensitive data and/or all the databases on the server are managed by the same people so having access across them doesn't pose any threat.

There isn't actually a stack load of information out there on this, so I thought i'd demonstrate how the flag can allow users access to databases that perhaps they shouldn't. Essentially, if the login which is the owner of the database (with the trustworthy flag) has permissions in other databases, then a different user in the database can create modules which access other databases by using the EXECUTE AS OWNER clause.

Look at this example:
NB: My example uses AdventureWorks but you can use any user database and modify the queries slightly

USE [master]
GO
-- create a sysadmin login
CREATE LOGIN MySYSAdmin
WITH PASSWORD = '123';
GO
EXEC sp_addsrvrolemember 'MySYSAdmin', 'sysadmin';
GO
-- impersonate that login to create the database
-- and therefore be the owner
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
CREATE DATABASE TrustworthyTest
GO
USE [master]
GO
-- now create a test user who has full privileges
-- within that database
CREATE LOGIN MyTrustworthyTestDBOwner
WITH PASSWORD = '456'
GO
USE TrustworthyTest
GO
CREATE USER [MyTrustworthyTestDBOwner]
FOR LOGIN [MyTrustworthyTestDBOwner]
GO
EXEC sp_addrolemember 'db_owner', 'MyTrustworthyTestDBOwner'
GO
USE [master]
GO
-- drop out of the sysadmin context
-- and lets impersonate our new user
REVERT
GO
USE [TrustworthyTest]
GO
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- can we select from another database? No!!
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- Msg 916, Level 14, State 1, Line 1
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- how about in a stored procedure?
CREATE PROCEDURE dbo.FetchFromAdventureWorks
WITH EXECUTE AS CALLER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO
-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- ok, lets revert back and reimpersonate our
-- sysadmin and make the database trustworthy
REVERT
GO
EXECUTE AS LOGIN = 'MySYSAdmin';
GO
ALTER DATABASE TrustworthyTest
SET TRUSTWORTHY ON
GO
REVERT
GO
-- and again impersonate the user
EXECUTE AS LOGIN = 'MyTrustworthyTestDBOwner'
GO
-- we still can't access the other database
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
-- Msg 916, Level 14, State 1, Line 2
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.

-- nor through the stored procedure
EXEC dbo.FetchFromAdventureWorks
GO
-- Msg 916, Level 14, State 1, Procedure FetchFromAdventureWorks, Line 4
-- The server principal "MyTrustworthyTestDBOwner" is not able to access the database "AdventureWorks" under the current security context.


-- BUT if we change the procedure to execute
-- in a different context. WE CAN!!!
ALTER PROCEDURE dbo.FetchFromAdventureWorks
WITH EXECUTE AS OWNER
AS
SELECT *
FROM AdventureWorks.HumanResources.Employee
GO
EXEC dbo.FetchFromAdventureWorks
GO

As I said, for many users of SQL Server having access across databases is not that big a deal and using the TRUSTWORTHY flag is preferable to creating keys and logins for CLR assemblies but its still important that you understand this behaviour.