Re: Security confusion with DB_CHAINING in 2005
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/17/05
- Next message: Louis Davidson: "How to contact a person at Microsoft about using their idea in your writing"
- Previous message: Jasper Smith: "Re: Security confusion with DB_CHAINING in 2005"
- In reply to: Jasper Smith: "Re: Security confusion with DB_CHAINING in 2005"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Thu, 17 Nov 2005 13:56:40 -0600
That is what I was missing. I didn't realize about the user (I set
TRUSTWORTHY ON just because I was poking around trying to figure it out, and
also so I could use the EXECUTE AS option. Thank you so much for the help,
I was really quite confused by this one.
-- ---------------------------------------------------------------------------- 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) "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message news:u8qPls66FHA.1416@TK2MSFTNGP09.phx.gbl... > 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: "How to contact a person at Microsoft about using their idea in your writing"
- Previous message: Jasper Smith: "Re: Security confusion with DB_CHAINING in 2005"
- In reply to: Jasper Smith: "Re: Security confusion with DB_CHAINING in 2005"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
