Somehow I failed to observe that there were two tables. Here is a query to meet your needs:

SELECT CM.Combinecourseid, coalesce(SC.subcourseid, CM.Courseid) AS Subcourseid

FROM #tblCombineCourseMaster CM

LEFT JOIN...

It is not clear why you don't want 110100009 to appear in the output. I will make the assumption that this an error in your expected output.

If I understand the...

I'm at a client and with limited time that I can help users in forum. And I did write this article so that I don't have to say the same...

If the views goes goes cross-database, that is you have views in database A that accesses tables in database B, and you want users to be able to access the...

This is possible, but it takes a bit of reading and understanding to get there. I have an article on my web site which discusses the option in details, but...

Yet a caveat with cross-DB chaining is that persons who have permissions to create users and procedures, can elevate their permissions to sysadmin, as I discuss in my article.

It is the same as if the user called a stored proceeure. That is ownership chaining applies etc.

As I said, certificate signing seems to be the way to go. I'm sorry, I don't have the time to elaboate now - and that's why I wrote an article...

Only granting access will not work, as User1 does not have access to DB2.

Certificate signing is probably the way to go. I have an article on my web...

Could there be any DENY in the AD?

How do you set the service account? You should use the SQL Server Configuration Manager, since this also grants the permissions needed.

Sue's answer is correct as long as you start do dabble with DENY or roles like db_denydatareader. In difference to sysadmin, you can deny db_owner rights. And DENY always takes...

I never said that it would be a quick thing. There is a reason that I wrote an aritcle about it, so I don't have to make a detailed explanation...

Certificate signing will not require any changes to the code. Have you looked at my article yet?