I got it! Just a regular statement with no ifs or cases. Thank you all so much for your help! This is what worked.

SELECT d.name, d.compatibility_level

FROM sys.database_mirroring AS M...

That is correct. But, all the ones I test are failing to bring back the desired result. I'll try with if statements.

Still only brings back the principal databases from mirrored servers.

I think I have the case statement in the wrong place?

My logic would be:

If it is mirrored:

Select database names from mirrored servers that are principal role

But if it is not:

select...

Here's my updated script with correct aliases and your suggestion.

Select

d.name,

d.compatibility_level

FROM

sys.database_mirroring M inner join SYS.DATABASES d

on m.database_id = d.database_id

where D.database_id >=5

...

Thanks Dixie! My oops on the referencing aliases.

When I run it on CMS it only brings me back the principal databases on the mirrored servers which is great!

But I...

I would replicate it in Development and/or backup in development if it already exists, then delete. If application has errors, restore. If not, migrate to production.

Wow! I never finished writing the messsage and must have accidentally sent because I got busy at work. I am a dba, not a developer which is why I am...

Because we need a join between the 2 servers for information.

I found the solution. It was that SSIS was not running as the same account sql services were running. Changing...

The linked server is using security to be made using the login's current security context. The SSIS Package is created and being tested using sql service. The query runs just...

Thank you! I apologize for any errors. I did not receive any on my end when originally creating it for one server.

@George-Sorry I've taken so long to reply. Thank you. I did vote. I'll be trying your suggestions.

@Martin- Thank you. I'll be trying your suggestions as well

This is too complicated for...

ok thanks! I'll try that out tomorrow or friday. 🙂

Awesome!Thanks!

WE dont want to give them sa because they have the ability to cvhange configurations and passwords to logins. Microsoft created this credential/proxy security. I'm assuming it has to work...