Security confusion with DB_CHAINING in 2005

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/17/05 

Date: Thu, 17 Nov 2005 12:21:47 -0600

Check the following script, it is driving me nutters. I cannot seem to get
chaining to work across database in RTM in any case (it was fine back in
Sept CTP). I can get impersonation to work to as you can see later in the
script.

Thanks!

USE MASTER
go
DROP DATABASE externalDb
DROP DATABASE localDb
go

CREATE DATABASE externalDb
GO
USE externalDb
GO
CREATE TABLE dbo.table1 ( value int )

GO
----------------------------------------------------------------------------
CREATE DATABASE localDb
GO
USE localDb
GO
CREATE LOGIN smurf WITH PASSWORD = 'La la, la la la la, la, la la la la'
CREATE USER smurf FROM LOGIN smurf
GO

--just to make absolute sure!
ALTER AUTHORIZATION ON DATABASE::localDb TO sa
ALTER AUTHORIZATION ON DATABASE::externalDb TO sa

ALTER DATABASE localDb
   SET DB_CHAINING ON
ALTER DATABASE localDb
   SET TRUSTWORTHY ON

ALTER DATABASE externalDb
   SET DB_CHAINING ON
ALTER DATABASE externalDb
   SET TRUSTWORTHY ON
GO
select cast(name as varchar(10)) as name, cast(suser_sname(owner_sid) as
varchar(10)) as owner,
        is_trustworthy_on, is_db_chaining_on
from sys.databases where name in ('localdb','externaldb')
/*
name owner is_trustworthy_on is_db_chaining_on
---------- ---------- ----------------- -----------------
externalDb sa 1 1
localDb sa 1 1
*/
----------------------------------------------------------------------------

CREATE PROCEDURE dbo.externalDb$testCrossDatabase
AS
SELECT Value
FROM externalDb.dbo.table1
GO
CREATE PROCEDURE dbo.externalDb$testCrossDatabase_Impersonation
WITH EXECUTE AS SELF --dbo
AS
SELECT Value
FROM externalDb.dbo.table1
GO

GRANT execute on dbo.externalDb$testCrossDatabase to smurf
GRANT execute on dbo.externalDb$testCrossDatabase_impersonation to smurf

GO
----------------------------------------------------------------------------

EXECUTE AS USER = 'smurf'
go
EXECUTE dbo.externalDb$testCrossDatabase

Returns:
Msg 916, Level 14, State 1, Procedure externalDb$testCrossDatabase, Line 3
The server principal "smurf" is not able to access the database "externalDb"
under the current security context.
GO
-----------------------------------------------------------------------------

EXECUTE dbo.externalDb$testCrossDatabase_impersonation
GO

Returns:

Value
----------- 

-- 
----------------------------------------------------------------------------
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing." 
(Oscar Wilde)

Relevant Pages

  • Re: Security confusion with DB_CHAINING in 2005
    ... also so I could use the EXECUTE AS option. ... > smurf still needs to be a user in externaldb (albeit with just membership ... >> get chaining to work across database in RTM in any case (it was fine back ... >> CREATE DATABASE externalDb ...
    (microsoft.public.sqlserver.security)
  • Re: Security confusion with DB_CHAINING in 2005
    ... trustworthy on for externaldb. ... > get chaining to work across database in RTM in any case (it was fine back ... > CREATE LOGIN smurf WITH PASSWORD = 'La la, la la la la, la, la la la la' ... > GRANT execute on dbo.externalDb$testCrossDatabase to smurf ...
    (microsoft.public.sqlserver.security)
  • help needed for Recovring database
    ... I wanted to move my database log file to another drive, ... alter database ABC2 modify file ... But when I execute following query, it displays different logical file name ...
    (microsoft.public.sqlserver.clients)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)