How does the view access TEST1 and TEST2? If you were to convert the view definition to a simple select statement and looked at the execution plan, does it look...

Nothing beats an awesome example! Loved it. 

Thank you.

There are a couple major issues.

1. Your string literals are missing quotation marks.

2. The style of case you used only supports simple constant equality comparisons. You are trying to use...

Actually, he doesn't want to grant table access to the user. He only wants to grant execute authority to the stored procedures (which is best). The example you gave simply...

If you have the option of creating a stored procedure inside database2 to perform the select then I guess that is the best option I can think of at this...

I'm going to guess that the batch stops because the prepare process crashed due to the remote server being responsible for helping to prepare the SQL. But the reason ultimately...

Try changing it to:

select * from database2.dbo.table1

where id = @id

I think SQL Server may consider .. to be an owner change and force a security check.

Good call Tal. I've actually done that a few times myself. For some reason I just didn't think about it.

That doesn't work as...

Assuming the number of results desired is stored within @MaxResults you can use the following:

set ROWCOUNT @MaxResults

You could use the TOP option as well, but since it is a variable...

The SP handled the error, but does not mask the error from the client. Since ignoring all errors from the client is a bad idea, you could create a second...

Does the owner of the stored procedure have the necessary rights to the table you are attempting to access in the other database? If not, you will need to grant...

If you are using EXEC( 'put some SQL here' ) or exec sp_executeSQL N'put some SQL here' in order to perform SQL it will run "dynamically". This allows you to...

I believe the owner needs to be the same. Is the owner of the SP the same as the owner of the table(s) in the other database?

Also, if you are...

How about using the osql utility. If you put the SQL scripts in a folder somewhere you can use a DOS "for each" loop to run osql for each of...

I'm not entirely sure what you are after, but one possible solution would be to use SQL Profiler to capture the plan information (the know option is there, but I've...