Re: Security confusion with DB_CHAINING in 2005

From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 11/17/05

  • Next message: Louis Davidson: "Re: Security confusion with DB_CHAINING in 2005" 
    Date: Thu, 17 Nov 2005 19:14:38 -0000

    smurf still needs to be a user in externaldb (albeit with just membership of
    the public role) for ownership chaining to work. Also you don't need to set
    trustworthy on for externaldb. 

    -- 
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message 
news:uNHpHP66FHA.3588@TK2MSFTNGP15.phx.gbl...
> 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)
>
>
  • Next message: Louis Davidson: "Re: Security confusion with DB_CHAINING in 2005"

    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)
    • Security confusion with DB_CHAINING in 2005
      ... DROP DATABASE externalDb ... CREATE USER smurf FROM LOGIN smurf ... ALTER DATABASE localDb ... GRANT execute on dbo.externalDb$testCrossDatabase to smurf ...
      (microsoft.public.sqlserver.security)