April 15, 2008 at 8:12 am
I have two tables A and B. Table A is in database X and table B is in database Y. Both tables contain columns that have sensitive data in them. Select rights have been denied on those columns in both tables.
Database access is done via stored procedures however there is a procedure in database A that reads data from both tables X & Y. Furthermore the stored procedure is reading data from the columns in both tables which contain sensitive data.
When executed, the procedure fails because of the access restrictions on the columns in table B in database Y.
If the stored procedure is set up to just access the columns with sensitive data in table A and is executed in Database X then it runs fine.
Is there any way to set up permissions so that read access is denied to the sensitive data in both tables but allowed when queried via the stored procedure? Creating a procedure in database Y and executing it within the stored procedure in database X is not an option.
All database objects owned by dbo in both databases. Sql Server 2000 Sp4.
Ex sproc:
create procedure dbo.ddsp_test
as
select a.sensitivedatacolum, count(*)
from dbo.a
join y.dbo.b on a.sensitivecolum = b.sensitivecolumn
group by a.sensitivecolumn
May 5, 2008 at 8:13 am
Sounds like you need to enable "Allow ownership chaining" under database options. Under SP4 this is by default disabled.
[font="Verdana"]Markus Bohse[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply