May 14, 2005 at 2:39 pm
Hi guys/gals/other,
I built a proc in one database to query a table in another database. Both databases (and the afore-montioned objects) are owned by the same person (me - the dbo). I added a test user in the database with the proc and gave it exec permission. However running the proc I get a "Server user 'internet_user' is not a valid user in database 'TTCUserInfo'"
Now I really don't want to have to grant access to TTCUserInfo for internet_user. I would much prefer internet_user only get its data via a call to my proc. I turned chaining on for both the proc database and the TTCUserInfo database which I figured should fix this problem. But it doesn't - any ideas?
Thanks in advance
May 15, 2005 at 4:23 am
Could you please post the code of your SP? I assume you are using the 3 part name to retrieve the data...
So you have a stored proc in db1 which effectively does a
select * from db2.dbo.table
?
May 15, 2005 at 3:05 pm
Sure - here's the whole enchilada
Use InternetUserArea
go
if exists( select * from sysobjects where name = 'GetUserInfo' )
drop proc dbo.GetUserInfo
go
create proc dbo.GetUserInfo(
@sUserLogin varchar(50),
@sPassword varchar(50)
)
as
begin
if exists( select * from TTCUserInfo.dbo.BasicUserInfo
where login_name = @sUserLogin and login_password = @sPassword )
select * from TTCUserInfo.dbo.BasicUserInfo
where login_name = @sUserLogin
else
select 0 as userid
end
go
grant exec on dbo.GetUserInfo to public
go
Thanks,
Kevin
May 19, 2005 at 7:26 pm
Kevin,
Try adding internet_user as a user in TTCUserInfo with no permissions.
Greg
Greg
May 19, 2005 at 8:50 pm
I did (rather defeats the purpose of chaining doesn't it) however, I still need to grant permissions to the individual objects to get it to work. There is a flag or configuration setting somewhere that appears to block all my attempts at chaining.
Time for a support call
June 1, 2005 at 3:46 pm
Kevin,
Sorry I haven't been able to post for the last couple of weeks. Did you resolve this problem. It occurred to me that this may be happening because you granted EXECUTE permission to the public role rather than to the internet_user user. Database ownership chaining shouldn't require permissions on the tables in the remote database.
Greg
Greg
June 1, 2005 at 5:29 pm
Hi Greg,
Same problem there. This is one of those cases where everything "seems" to be okay but it is not working as advertised. I have moved onto other things in the meantime although I will probably put in a support call before too long. I suppose I could create a role for internet users although I was intending public to be the same thing. I think there is a flag somewhere I set which is overriding the chaining. Maybe something at the OS level I am missing.
Thanks for trying,
Kevin
June 2, 2005 at 12:47 am
Are the two databases owned by the same SQL Server login account?
June 2, 2005 at 8:06 am
Yes - both were created by the same domain admin account and show the owner to be sa
Kevin
June 3, 2005 at 2:34 am
Have you tried allowing cross-database ownership chaining for the entire server? Not that it should do any difference, since you have it activated for the databases in question already.
June 3, 2005 at 4:42 am
Yes I did, argh! Still not working. Since it would appear to be working for others, I figure it is probably time to raise the white flag.
June 3, 2005 at 6:33 am
If you could produce a small repro then it would be easier to figure out what is going on. In fact, I think that while producing a repro I would almost guess you would find the solution yourself, from looking at the problem with 'fresh eyes' and a new angle.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply