Why the stored procedure cannot read the table from another database (I should mistakenly use GRANT and DENY)

Question

I have two Microsoft SQL Server 2000 databases, and a stored procedure on one tries to read data from another. This worked fine, but since I became conscious and changed the login (SQL user) from "owner db" to "denydatareader", the call failed.

I can make everything work if I use the "datareader" group, but since I do not want this login to have read access to user tables (ASPs use only procs), I thought it was unreasonable. It also works if I remove a user from all groups !!! This is normal?

---

One database is called "Internal" and has a table called "Stuff". The other is called "WebFacing" and has a stored procedure called "Get_Some_Data" that selected "Internal..Stuff".

I ran this command in the internal database:
  GRANT SELECT ON Stuff TO magnus

I ran this in the WebFacing database:
  GRANT EXECUTE ON Get_Some_Data TO magnus

My ASP uses the SQL login "magnus" and connects to the "WebFacing" database. When he tries to execute the EXEC procedure, these are errors with:
SELECT permission denied on object 'Stuff', database 'Internal', owner 'dbo'.

---

(Sorry if this is a stupid question, but I was stooped at the deep end and only yesterday found out about GRANT and DENY. I tried Google) ...

Answer 1

SQL / ( ) , . , , /.

, , SQL Server, , , , , SQL, proc, SQL . ( proc) "", . MySQL, , , SQL Server, - CALLER, proc. , , EXEC CALLER. , , , proc, , , .

SQL Server 2005 EXEC CALLER, , . , "" -, , , , , ( , , ).

, , . SQL Server , . .

Answer 2

Croos-Database, , sproc. . , :

dbname set db_chaining on

Answer 3

, "commmand" , , .

, , SP, SELECT , SP, EXEC.

EXECUTE AS , , VIEW , , , , SP ( , ).

Answer 4

!

, ?

, () , , GRANTed.