I am trying to set-up the following scenario: We have a centralized
set of databases with access restricted to a select few. Let's call
one of those databases CentralDB. We have many applications that will
access this data in a read only manner and to keep the applications
separate from one another we place them in their own databases. Let's
call one ApplicationB. Both databases have had their ownership
changed to 'sa' and they both have 'db chaining' enabled. I have a
table within the CentralDB, AllData, which was created under the dbo
schema and looks like this: dbo.AllData. A view was created in the
ApplicationB database, under the dbo schema and looks like this:
dbo.TheView. The view structure is as follows:

Create View [dbo].[TheView]
(Name)
as
select col1
from CentralDB.dbo.AllData

Within the ApplicationB database I have a user, AppUser, which is a
sql server authenticated login and exists with data_reader/data_writer
permission in ApplicationB database only. I grant select on TheView
to AppUser. When I try to run the select I get the following error:

Msg 916, Level 14, State 1, Line 1
The server principal "AppUser" is not able to access the database
"CentralDB" under the current security context.

I have read every article I can find on this topic and I cannot see
why this is failing. The databases are both owned by SA and both the
table and view are created using the dbo schema and are therefor owned
by dbo. I have granted select permission on the view, TheView, to
AppUser. My guess is that somewhere along the way the chain is being
broken. Does anyone have any ideas what is failing? I do understand
that I can simply add AppUser to the CentralDB, but the purpose of
this set-up was to have applications be self-contained and restrict
access to CentralDB.


Thanks,
Josef

chumshack,

What is the default schema of that user?


AMB

"chumshack" wrote:

> I am trying to set-up the following scenario: We have a centralized
> set of databases with access restricted to a select few. Let's call
> one of those databases CentralDB. We have many applications that will
> access this data in a read only manner and to keep the applications
> separate from one another we place them in their own databases. Let's
> call one ApplicationB. Both databases have had their ownership
> changed to 'sa' and they both have 'db chaining' enabled. I have a
> table within the CentralDB, AllData, which was created under the dbo
> schema and looks like this: dbo.AllData. A view was created in the
> ApplicationB database, under the dbo schema and looks like this:
> dbo.TheView. The view structure is as follows:
>
> Create View [dbo].[TheView]
> (Name)
> as
> select col1
> from CentralDB.dbo.AllData
>
> Within the ApplicationB database I have a user, AppUser, which is a
> sql server authenticated login and exists with data_reader/data_writer
> permission in ApplicationB database only. I grant select on TheView
> to AppUser. When I try to run the select I get the following error:
>
> Msg 916, Level 14, State 1, Line 1
> The server principal "AppUser" is not able to access the database
> "CentralDB" under the current security context.
>
> I have read every article I can find on this topic and I cannot see
> why this is failing. The databases are both owned by SA and both the
> table and view are created using the dbo schema and are therefor owned
> by dbo. I have granted select permission on the view, TheView, to
> AppUser. My guess is that somewhere along the way the chain is being
> broken. Does anyone have any ideas what is failing? I do understand
> that I can simply add AppUser to the CentralDB, but the purpose of
> this set-up was to have applications be self-contained and restrict
> access to CentralDB.
>
>
> Thanks,
> Josef
>

On May 21, 1:10pm, Alejandro Mesa
<AlejandroM...@discussions.microsoft.com> wrote:
> chumshack,
>
> What is the default schema of that user?
>
> AMB
>
>
>
> "chumshack" wrote:
> > I am trying to set-up the following scenario: We have a centralized
> > set of databases with access restricted to a select few. Let's call
> > one of those databases CentralDB. We have many applications that will
> > access this data in a read only manner and to keep the applications
> > separate from one another we place them in their own databases. Let's
> > call one ApplicationB. Both databases have had their ownership
> > changed to 'sa' and they both have 'db chaining' enabled. I have a
> > table within the CentralDB, AllData, which was created under the dbo
> > schema and looks like this: dbo.AllData. A view was created in the
> > ApplicationB database, under the dbo schema and looks like this:
> > dbo.TheView. The view structure is as follows:
>
> > Create View [dbo].[TheView]
> > (Name)
> > as
> > select col1
> > from CentralDB.dbo.AllData
>
> > Within the ApplicationB database I have a user, AppUser, which is a
> > sql server authenticated login and exists with data_reader/data_writer
> > permission in ApplicationB database only. I grant select on TheView
> > to AppUser. When I try to run the select I get the following error:
>
> > Msg 916, Level 14, State 1, Line 1
> > The server principal "AppUser" is not able to access the database
> > "CentralDB" under the current security context.
>
> > I have read every article I can find on this topic and I cannot see
> > why this is failing. The databases are both owned by SA and both the
> > table and view are created using the dbo schema and are therefor owned
> > by dbo. I have granted select permission on the view, TheView, to
> > AppUser. My guess is that somewhere along the way the chain is being
> > broken. Does anyone have any ideas what is failing? I do understand
> > that I can simply add AppUser to the CentralDB, but the purpose of
> > this set-up was to have applications be self-contained and restrict
> > access to CentralDB.
>
> > Thanks,
> > Josef- Hide quoted text -
>
> - Show quoted text -

dbo

> I have read every article I can find on this topic and I cannot see
> why this is failing. The databases are both owned by SA and both the
> table and view are created using the dbo schema and are therefor owned
> by dbo. I have granted select permission on the view, TheView, to
> AppUser. My guess is that somewhere along the way the chain is being
> broken. Does anyone have any ideas what is failing? I do understand
> that I can simply add AppUser to the CentralDB, but the purpose of
> this set-up was to have applications be self-contained and restrict
> access to CentralDB.

A user must have a security context in order to access a database. If you
don't want to add the user to CentralDB, you can enable the guest user
instead. No permissions need to be granted on the table in either case.

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"chumshack" <sqlrunner@gmail.com> wrote in message
news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
>I am trying to set-up the following scenario: We have a centralized
> set of databases with access restricted to a select few. Let's call
> one of those databases CentralDB. We have many applications that will
> access this data in a read only manner and to keep the applications
> separate from one another we place them in their own databases. Let's
> call one ApplicationB. Both databases have had their ownership
> changed to 'sa' and they both have 'db chaining' enabled. I have a
> table within the CentralDB, AllData, which was created under the dbo
> schema and looks like this: dbo.AllData. A view was created in the
> ApplicationB database, under the dbo schema and looks like this:
> dbo.TheView. The view structure is as follows:
>
> Create View [dbo].[TheView]
> (Name)
> as
> select col1
> from CentralDB.dbo.AllData
>
> Within the ApplicationB database I have a user, AppUser, which is a
> sql server authenticated login and exists with data_reader/data_writer
> permission in ApplicationB database only. I grant select on TheView
> to AppUser. When I try to run the select I get the following error:
>
> Msg 916, Level 14, State 1, Line 1
> The server principal "AppUser" is not able to access the database
> "CentralDB" under the current security context.
>
> I have read every article I can find on this topic and I cannot see
> why this is failing. The databases are both owned by SA and both the
> table and view are created using the dbo schema and are therefor owned
> by dbo. I have granted select permission on the view, TheView, to
> AppUser. My guess is that somewhere along the way the chain is being
> broken. Does anyone have any ideas what is failing? I do understand
> that I can simply add AppUser to the CentralDB, but the purpose of
> this set-up was to have applications be self-contained and restrict
> access to CentralDB.
>
>
> Thanks,
> Josef

On May 21, 8:52pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > I have read every article I can find on this topic and I cannot see
> > why this is failing. The databases are both owned by SA and both the
> > table and view are created using the dbo schema and are therefor owned
> > by dbo. I have granted select permission on the view, TheView, to
> > AppUser. My guess is that somewhere along the way the chain is being
> > broken. Does anyone have any ideas what is failing? I do understand
> > that I can simply add AppUser to the CentralDB, but the purpose of
> > this set-up was to have applications be self-contained and restrict
> > access to CentralDB.
>
> A user must have a security context in order to access a database. If you
> don't want to add the user to CentralDB, you can enable the guest user
> instead. No permissions need to be granted on the table in either case.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> "chumshack" <sqlrun...@gmail.com> wrote in message
>
> news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
>
>
>
> >I am trying to set-up the following scenario: We have a centralized
> > set of databases with access restricted to a select few. Let's call
> > one of those databases CentralDB. We have many applications that will
> > access this data in a read only manner and to keep the applications
> > separate from one another we place them in their own databases. Let's
> > call one ApplicationB. Both databases have had theirownership
> > changed to 'sa' and they both have 'dbchaining' enabled. I have a
> > table within the CentralDB, AllData, which was created under the dbo
> > schema and looks like this: dbo.AllData. A view was created in the
> > ApplicationB database, under the dbo schema and looks like this:
> > dbo.TheView. The view structure is as follows:
>
> > Create View [dbo].[TheView]
> > (Name)
> > as
> > select col1
> > from CentralDB.dbo.AllData
>
> > Within the ApplicationB database I have a user, AppUser, which is a
> > sql server authenticated login and exists with data_reader/data_writer
> > permission in ApplicationB database only. I grant select on TheView
> > to AppUser. When I try to run the select I get the following error:
>
> > Msg 916, Level 14, State 1, Line 1
> > The server principal "AppUser" is not able to access the database
> > "CentralDB" under the current security context.
>
> > I have read every article I can find on this topic and I cannot see
> > why this is failing. The databases are both owned by SA and both the
> > table and view are created using the dbo schema and are therefor owned
> > by dbo. I have granted select permission on the view, TheView, to
> > AppUser. My guess is that somewhere along the way the chain is being
> > broken. Does anyone have any ideas what is failing? I do understand
> > that I can simply add AppUser to the CentralDB, but the purpose of
> > this set-up was to have applications be self-contained and restrict
> > access to CentralDB.
>
> > Thanks,
> > Josef- Hide quoted text -
>
> - Show quoted text -

The guest user does exist within the CentralDB, so the question I have
now is how to I link the user, and all users outside of CentralDB, to
use the guest user account when moving between databases?

On May 22, 8:29am, chumshack <sqlrun...@gmail.com> wrote:
> On May 21, 8:52pm, "Dan Guzman" <guzma...@nospam-
>
>
>
>
>
> online.sbcglobal.net> wrote:
> > > I have read every article I can find on this topic and I cannot see
> > > why this is failing. The databases are both owned by SA and both the
> > > table and view are created using the dbo schema and are therefor owned
> > > by dbo. I have granted select permission on the view, TheView, to
> > > AppUser. My guess is that somewhere along the way the chain is being
> > > broken. Does anyone have any ideas what is failing? I do understand
> > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > this set-up was to have applications be self-contained and restrict
> > > access to CentralDB.
>
> > A user must have a security context in order to access a database. Ifyou
> > don't want to add the user to CentralDB, you can enable the guest user
> > instead. No permissions need to be granted on the table in either case.
>
> > --
> > Hope this s.
>
> > Dan Guzman
> > SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> > "chumshack" <sqlrun...@gmail.com> wrote in message
>
> >news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
>
> > >I am trying to set-up the following scenario: We have a centralized
> > > set of databases with access restricted to a select few. Let's call
> > > one of those databases CentralDB. We have many applications that will
> > > access this data in a read only manner and to keep the applications
> > > separate from one another we place them in their own databases. Let's
> > > call one ApplicationB. Both databases have had theirownership
> > > changed to 'sa' and they both have 'dbchaining' enabled. I have a
> > > table within the CentralDB, AllData, which was created under the dbo
> > > schema and looks like this: dbo.AllData. A view was created in the
> > > ApplicationB database, under the dbo schema and looks like this:
> > > dbo.TheView. The view structure is as follows:
>
> > > Create View [dbo].[TheView]
> > > (Name)
> > > as
> > > select col1
> > > from CentralDB.dbo.AllData
>
> > > Within the ApplicationB database I have a user, AppUser, which is a
> > > sql server authenticated login and exists with data_reader/data_writer
> > > permission in ApplicationB database only. I grant select on TheView
> > > to AppUser. When I try to run the select I get the following error:
>
> > > Msg 916, Level 14, State 1, Line 1
> > > The server principal "AppUser" is not able to access the database
> > > "CentralDB" under the current security context.
>
> > > I have read every article I can find on this topic and I cannot see
> > > why this is failing. The databases are both owned by SA and both the
> > > table and view are created using the dbo schema and are therefor owned
> > > by dbo. I have granted select permission on the view, TheView, to
> > > AppUser. My guess is that somewhere along the way the chain is being
> > > broken. Does anyone have any ideas what is failing? I do understand
> > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > this set-up was to have applications be self-contained and restrict
> > > access to CentralDB.
>
> > > Thanks,
> > > Josef- Hide quoted text -
>
> > - Show quoted text -
>
> The guest user does exist within the CentralDB, so the question I have
> now is how to I link the user, and all users outside of CentralDB, to
> use the guest user account when moving between databases?- Hide quoted text -
>
> - Show quoted text -

NVM, I got it thank you for the Dan, it fixed the issue.

I'm glad you were able to get it going.

--
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"chumshack" <sqlrunner@gmail.com> wrote in message
news:f9cc20a1-f493-4750-a95f-c183bc3c411e@8g2000hse.googlegroups.com...
On May 22, 8:29 am, chumshack <sqlrun...@gmail.com> wrote:
> On May 21, 8:52 pm, "Dan Guzman" <guzma...@nospam-
>
>
>
>
>
> online.sbcglobal.net> wrote:
> > > I have read every article I can find on this topic and I cannot see
> > > why this is failing. The databases are both owned by SA and both the
> > > table and view are created using the dbo schema and are therefor owned
> > > by dbo. I have granted select permission on the view, TheView, to
> > > AppUser. My guess is that somewhere along the way the chain is being
> > > broken. Does anyone have any ideas what is failing? I do understand
> > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > this set-up was to have applications be self-contained and restrict
> > > access to CentralDB.
>
> > A user must have a security context in order to access a database. If
> > you
> > don't want to add the user to CentralDB, you can enable the guest user
> > instead. No permissions need to be granted on the table in either case.
>
> > --
> > Hope this s.
>
> > Dan Guzman
> > SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> > "chumshack" <sqlrun...@gmail.com> wrote in message
>
> >news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
>
> > >I am trying to set-up the following scenario: We have a centralized
> > > set of databases with access restricted to a select few. Let's call
> > > one of those databases CentralDB. We have many applications that will
> > > access this data in a read only manner and to keep the applications
> > > separate from one another we place them in their own databases. Let's
> > > call one ApplicationB. Both databases have had theirownership
> > > changed to 'sa' and they both have 'dbchaining' enabled. I have a
> > > table within the CentralDB, AllData, which was created under the dbo
> > > schema and looks like this: dbo.AllData. A view was created in the
> > > ApplicationB database, under the dbo schema and looks like this:
> > > dbo.TheView. The view structure is as follows:
>
> > > Create View [dbo].[TheView]
> > > (Name)
> > > as
> > > select col1
> > > from CentralDB.dbo.AllData
>
> > > Within the ApplicationB database I have a user, AppUser, which is a
> > > sql server authenticated login and exists with data_reader/data_writer
> > > permission in ApplicationB database only. I grant select on TheView
> > > to AppUser. When I try to run the select I get the following error:
>
> > > Msg 916, Level 14, State 1, Line 1
> > > The server principal "AppUser" is not able to access the database
> > > "CentralDB" under the current security context.
>
> > > I have read every article I can find on this topic and I cannot see
> > > why this is failing. The databases are both owned by SA and both the
> > > table and view are created using the dbo schema and are therefor owned
> > > by dbo. I have granted select permission on the view, TheView, to
> > > AppUser. My guess is that somewhere along the way the chain is being
> > > broken. Does anyone have any ideas what is failing? I do understand
> > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > this set-up was to have applications be self-contained and restrict
> > > access to CentralDB.
>
> > > Thanks,
> > > Josef- Hide quoted text -
>
> > - Show quoted text -
>
> The guest user does exist within the CentralDB, so the question I have
> now is how to I link the user, and all users outside of CentralDB, to
> use the guest user account when moving between databases?- Hide quoted
> text -
>
> - Show quoted text -

NVM, I got it thank you for the Dan, it fixed the issue.

OK you were able to get it going; but HOW?
I have gone through all the same scenario and can't get this working.



"Dan Guzman" wrote:

> I'm glad you were able to get it going.
>
> --
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "chumshack" <sqlrunner@gmail.com> wrote in message
> news:f9cc20a1-f493-4750-a95f-c183bc3c411e@8g2000hse.googlegroups.com...
> On May 22, 8:29 am, chumshack <sqlrun...@gmail.com> wrote:
> > On May 21, 8:52 pm, "Dan Guzman" <guzma...@nospam-
> >
> >
> >
> >
> >
> > online.sbcglobal.net> wrote:
> > > > I have read every article I can find on this topic and I cannot see
> > > > why this is failing. The databases are both owned by SA and both the
> > > > table and view are created using the dbo schema and are therefor owned
> > > > by dbo. I have granted select permission on the view, TheView, to
> > > > AppUser. My guess is that somewhere along the way the chain is being
> > > > broken. Does anyone have any ideas what is failing? I do understand
> > > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > > this set-up was to have applications be self-contained and restrict
> > > > access to CentralDB.
> >
> > > A user must have a security context in order to access a database. If
> > > you
> > > don't want to add the user to CentralDB, you can enable the guest user
> > > instead. No permissions need to be granted on the table in either case.
> >
> > > --
> > > Hope this s.
> >
> > > Dan Guzman
> > > SQL Server MVPhttp://weblogs.sqlteam.com/dang/
> >
> > > "chumshack" <sqlrun...@gmail.com> wrote in message
> >
> > >news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
> >
> > > >I am trying to set-up the following scenario: We have a centralized
> > > > set of databases with access restricted to a select few. Let's call
> > > > one of those databases CentralDB. We have many applications that will
> > > > access this data in a read only manner and to keep the applications
> > > > separate from one another we place them in their own databases. Let's
> > > > call one ApplicationB. Both databases have had theirownership
> > > > changed to 'sa' and they both have 'dbchaining' enabled. I have a
> > > > table within the CentralDB, AllData, which was created under the dbo
> > > > schema and looks like this: dbo.AllData. A view was created in the
> > > > ApplicationB database, under the dbo schema and looks like this:
> > > > dbo.TheView. The view structure is as follows:
> >
> > > > Create View [dbo].[TheView]
> > > > (Name)
> > > > as
> > > > select col1
> > > > from CentralDB.dbo.AllData
> >
> > > > Within the ApplicationB database I have a user, AppUser, which is a
> > > > sql server authenticated login and exists with data_reader/data_writer
> > > > permission in ApplicationB database only. I grant select on TheView
> > > > to AppUser. When I try to run the select I get the following error:
> >
> > > > Msg 916, Level 14, State 1, Line 1
> > > > The server principal "AppUser" is not able to access the database
> > > > "CentralDB" under the current security context.
> >
> > > > I have read every article I can find on this topic and I cannot see
> > > > why this is failing. The databases are both owned by SA and both the
> > > > table and view are created using the dbo schema and are therefor owned
> > > > by dbo. I have granted select permission on the view, TheView, to
> > > > AppUser. My guess is that somewhere along the way the chain is being
> > > > broken. Does anyone have any ideas what is failing? I do understand
> > > > that I can simply add AppUser to the CentralDB, but the purpose of
> > > > this set-up was to have applications be self-contained and restrict
> > > > access to CentralDB.
> >
> > > > Thanks,
> > > > Josef- Hide quoted text -
> >
> > > - Show quoted text -
> >
> > The guest user does exist within the CentralDB, so the question I have
> > now is how to I link the user, and all users outside of CentralDB, to
> > use the guest user account when moving between databases?- Hide quoted
> > text -
> >
> > - Show quoted text -
>
> NVM, I got it thank you for the Dan, it fixed the issue.
>

> OK you were able to get it going; but HOW?
> I have gone through all the same scenario and can't get this working.

Below is a demo script (gleaned from another thread today) that shows how to
set cross-database chaining using a view. Note that db chaining should be
enabled in an sa-owned database when only sysadmin role members can create
dbo-owned objects in the database.

-- SETUP test login
CREATE LOGIN Bob WITH PASSWORD = 'password*1';

-- SETUP SalesDB
CREATE DATABASE SalesDB WITH DB_CHAINING ON;
ALTER AUTHORIZATION ON DATABASE::SalesDB TO sa;
GO
USE SalesDB;
GO
CREATE USER guest; --or create user account in this database
CREATE TABLE Sales (TransactionID int, Amount smallmoney);
INSERT INTO Sales (TransactionID, Amount) VALUES (1, 123.45);
GO

-- SETUP ReportsDB
CREATE DATABASE ReportsDB WITH DB_CHAINING ON;
ALTER AUTHORIZATION ON DATABASE::ReportsDB TO sa;
GO
USE ReportsDB;
CREATE USER Bob;
GO
CREATE VIEW Sales AS SELECT * FROM SalesDB..Sales;
GO
GRANT SELECT ON Sales TO Bob
GO

--test permissions
EXECUTE AS login = 'Bob'
GO
SELECT * FROM Sales --this should succeed
GO
SELECT * FROM SalesDB..Sales --this should fail
GO
REVERT
GO

/*
-- CLEAN UP
USE master
DROP DATABASE SalesDB;
DROP DATABASE ReportsDB;
DROP LOGIN Bob;
*/


--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Warren" <Warren @discussions.microsoft.com> wrote in message
news:A61B0C64-1E71-4253-9925-370E586F0D74@microsoft.com...
> OK you were able to get it going; but HOW?
> I have gone through all the same scenario and can't get this working.
>
>
>
> "Dan Guzman" wrote:
>
>> I'm glad you were able to get it going.
>>
>> --
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "chumshack" <sqlrunner@gmail.com> wrote in message
>> news:f9cc20a1-f493-4750-a95f-c183bc3c411e@8g2000hse.googlegroups.com...
>> On May 22, 8:29 am, chumshack <sqlrun...@gmail.com> wrote:
>> > On May 21, 8:52 pm, "Dan Guzman" <guzma...@nospam-
>> >
>> >
>> >
>> >
>> >
>> > online.sbcglobal.net> wrote:
>> > > > I have read every article I can find on this topic and I cannot see
>> > > > why this is failing. The databases are both owned by SA and both
>> > > > the
>> > > > table and view are created using the dbo schema and are therefor
>> > > > owned
>> > > > by dbo. I have granted select permission on the view, TheView, to
>> > > > AppUser. My guess is that somewhere along the way the chain is
>> > > > being
>> > > > broken. Does anyone have any ideas what is failing? I do understand
>> > > > that I can simply add AppUser to the CentralDB, but the purpose of
>> > > > this set-up was to have applications be self-contained and restrict
>> > > > access to CentralDB.
>> >
>> > > A user must have a security context in order to access a database. If
>> > > you
>> > > don't want to add the user to CentralDB, you can enable the guest
>> > > user
>> > > instead. No permissions need to be granted on the table in either
>> > > case.
>> >
>> > > --
>> > > Hope this s.
>> >
>> > > Dan Guzman
>> > > SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>> >
>> > > "chumshack" <sqlrun...@gmail.com> wrote in message
>> >
>> > >news:4b075fbf-c55f-4de9-8bad-0c783f293d02@m44g2000hsc.googlegroups.com...
>> >
>> > > >I am trying to set-up the following scenario: We have a centralized
>> > > > set of databases with access restricted to a select few. Let's call
>> > > > one of those databases CentralDB. We have many applications that
>> > > > will
>> > > > access this data in a read only manner and to keep the applications
>> > > > separate from one another we place them in their own databases.
>> > > > Let's
>> > > > call one ApplicationB. Both databases have had theirownership
>> > > > changed to 'sa' and they both have 'dbchaining' enabled. I have a
>> > > > table within the CentralDB, AllData, which was created under the
>> > > > dbo
>> > > > schema and looks like this: dbo.AllData. A view was created in the
>> > > > ApplicationB database, under the dbo schema and looks like this:
>> > > > dbo.TheView. The view structure is as follows:
>> >
>> > > > Create View [dbo].[TheView]
>> > > > (Name)
>> > > > as
>> > > > select col1
>> > > > from CentralDB.dbo.AllData
>> >
>> > > > Within the ApplicationB database I have a user, AppUser, which is a
>> > > > sql server authenticated login and exists with
>> > > > data_reader/data_writer
>> > > > permission in ApplicationB database only. I grant select on TheView
>> > > > to AppUser. When I try to run the select I get the following error:
>> >
>> > > > Msg 916, Level 14, State 1, Line 1
>> > > > The server principal "AppUser" is not able to access the database
>> > > > "CentralDB" under the current security context.
>> >
>> > > > I have read every article I can find on this topic and I cannot see
>> > > > why this is failing. The databases are both owned by SA and both
>> > > > the
>> > > > table and view are created using the dbo schema and are therefor
>> > > > owned
>> > > > by dbo. I have granted select permission on the view, TheView, to
>> > > > AppUser. My guess is that somewhere along the way the chain is
>> > > > being
>> > > > broken. Does anyone have any ideas what is failing? I do understand
>> > > > that I can simply add AppUser to the CentralDB, but the purpose of
>> > > > this set-up was to have applications be self-contained and restrict
>> > > > access to CentralDB.
>> >
>> > > > Thanks,
>> > > > Josef- Hide quoted text -
>> >
>> > > - Show quoted text -
>> >
>> > The guest user does exist within the CentralDB, so the question I have
>> > now is how to I link the user, and all users outside of CentralDB, to
>> > use the guest user account when moving between databases?- Hide quoted
>> > text -
>> >
>> > - Show quoted text -
>>
>> NVM, I got it thank you for the Dan, it fixed the issue.
>>