So now 'Find Invalid Objects' no longer works

Ozzie

After the generous download of SQL Prompt to get everything working again I am seeing that 'Find Invalid Objects' no longer works - the screen comes up - it sees the server - but the second dropdown is not populated with any databases.

Ideas? My product is registered with my personal email ( since I own the product) , but when I am on the server, I am logged in with my work address. I can provide more information.

Best,
Doug

cperez

Hi Ozzie,

Thanks for reaching out to Redgate Support, sorry to hear you are having some issues with your SQL prompt install. Would you be able to share your SQL Prompt logs here so we can take a look for any errors? (SQL Prompt > Help > Locate Log Files) Also a screenshot of what you are seeing when the issue replicates would be helpful as well.

Cheers,

Product Support

Ozzie

Files and screen shot attached - thank you!

Doug

cperez

Hi Ozzie, Thanks for the requested files, looking at the screenshot as well as the logs you provided I am seeing an interesting error for ds-sql-database.database.windows.net. I believe this is the same server in the screenshot you provided, could you confirm you do indeed have access to these servers and can load suggestions or is everything locked down for this server with respect to Prompt?

 [Warning] Failed to load server ds-sql-database.database.windows.net Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user token-identified principal

Ozzie

Dear CPerez,

The product is licensed to me. I log into the network DB via MFA utilizing my company login.

I cannot get 'Find Invalid Objects' to work on the network DB, but I can get it to work on my local DB.

What privilege do I need for this to function correctly?

Thanks,
Doug

cperez

Hi Ozzie,

For a list of priviliges SQL Prompt may need you can find that listed HERE.

That being said the one I would double check first would be the VIEW ANY DEFINITION permission which will let you view all metadata in the SQL Server instance, you can then check with

1. SELECT srvname, srvproduct, providername FROM master..sysservers

to confirm if the permission is now set correctly. 

Check the above permissions and let me know if you already had them set correctly.