application role how query a view whose reference table is in another db?

Hi everybody.

I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1

I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).

With the following query

SELECT USER_NAME()

I see that the approle is being used.

Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:

The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.

What should I do to make it work?

The table in DB2 has the same schema of the view in DB1 which refers to it.

I put the DB1 TrustWorthy and both the database have the db_chaining option activated.

Any idea on how to solve the problem would be widely appreciated.

Thank you very much.

Vania

Application roles are not designed to work on cross-database scenarios. The following post has a detailed discussion on the topic: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1312863&SiteID=1

If your application relies heavily on cross-database access, I would recommend against using application roles. If you don’ have a choice, you can create a new execution context using EXECUTE AS in a SP. I wrote a small example that hopefully will be useful:

-- We will strat creating a low privielged login

--that will be used for the cross-DB context

-- and disable it to prevent any connection with it

--

CREATE LOGIN crossdb_login WITH PASSWORD = '50m3 p4zzwrd#'

ALTER LOGIN crossdb_login DISABLE

go

-- First we will go to our source DB to create the SP

-- and digitally sign it

--

use db_Source

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

go

-- This SP will run under the 'crossdb_user' context

--

CREATE PROC sp_demo

WITH EXECUTE AS 'crossdb_user'

AS

-- display the token for testing purposes

SELECT * FROM db_Target.sys.user_token

-- select from a table

SELECT * FROM db_Target.dbo.data

go

-- Sign the modul and create a backup of the cert

-- in order to create a login for it

--

CREATE CERTIFICATE signing_cert

ENCRYPTION BY PASSWORD = '50m3 p4zzwrd#'

WITH SUBJECT = 'signing cert'

go

ADD SIGNATURE TO sp_demo

BY CERTIFICATE signing_cert

WITH PASSWORD = '50m3 p4zzwrd#'

go

BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'

go

-- Create a copy of the cert in master and a login mapped to it

--

USE master

go

CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'

go

CREATE LOGIN signing_cert FROM CERTIFICATE signing_cert

go

-- Now in the target DB we will create the user we need

-- and grant the appropriate permissions

--

use db_Target

go

CREATE TABLE data( value int )

go

CREATE USER crossdb_user FOR LOGIN crossdb_login

CREATE USER signing_cert

go

-- The certificate will be used to vouch for the

-- impersonated context

--

GRANT AUTHENTICATE TO signing_cert

go

-- GRANT SELECT on the table to the impersonated context

--

GRANT SELECT ON dbo.data to crossdb_user

go

-- Test the new signed SP

--

use db_Source

go

CREATE APPLICATION ROLE approle_demo WITH PASSWORD = '@.ppr0l3 d3m00'

go

GRANT EXECUTE ON sp_demo to approle_demo

go

EXEC sp_setapprole 'approle_demo', '@.ppr0l3 d3m00'

go

-- Should fail

-- Msg 916, Level 14, State 1, Line 1

-- The server principal "<<login_name>>" is not able to access the database "db_Target" under the current security context.

--

SELECT * FROM db_Target.dbo.data

go

-- Should succeed

EXEC sp_demo

go

Please let us know if you have any questions.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thank you so much and forgive me for my late but was on holidays.

Your solution works fine, in fact (I am sure you have no doubts about it).

A more question, please forgive me if it is obvious:

how, from inside the sp_demo, can I get the name of the server principal (domain\user if Windows authentication) which set the application role ? I mean the caller of the setapprole.

Thank you very much.

Vania