Runas User and Export to Excel

Brian_M

I sometimes have to log into SQL servers using an administrator account and when I go to export to Excel it automatically tries to open the file using the runas user (local computer admin). That user does not have an Excel license (Office 365) and I basically get stuck... I end up having to force cancel Excel (I cannot login to the MS account using my normal user nor my local admin user for whatever reason - it just hangs).

So the question is can the Export to Excel simply save the file vs. auto-opening? Or is there a different way around the Export to Excel functionality when dealing with this scenario?

Jon_Kirkwood

Hi @Brian_M

 

Thank you for reaching out on the Redgate forums regarding this Open in Excel functionality in SQL Prompt.

"Open in Excel" does attempt to use the user's account that has opened SSMS and triggered the process to open Excel.

 There may be some workarounds to get a desired outcome 

• The process saves a generated *.xlsx file into the users Local\Temp folder which you may be able to access whilst Excel is open.

In C:\Users\<User>\AppData\Local\Temp a temporary folder is created that holds the *.xlsx file. 

It would be possible to locate the folder and extract the file from here prior to closing Excel.

 

• In the results window, it is possible to right-click and copy the results. This could be pasted into an open Excel window that is active using your normal users account
  • Right-click > Select All | Copy with Headers

 

• You can possibly save the results as a comma or tab delimited file type and open that in Excel
  • Right-click > Save Results As > *.csv |  *.txt

 

 If these options aren't suitable, then the last suggestion would be to log a feature request on our UserVoice page. 

The feature request page for SQL Prompt can be found here - redgate.uservoice.com/forums/94413-sql-prompt/ - and is frequented by our development & product teams to guide the implementation of new features & functionality into our products.

Brian_M

Thanks for your reply Jon.

Copy with headers is not great options because often my data has CR/LF in it, which does not work well. Save as CSV does work decently, but the formatting is different than if I am able to just open in Excel. A Save as XLSX would be a great option.

That being said, I think I found an interesting workaround by first opening the admin version of SSMS, then opening SSMS under my normal account. For whatever reason it now allows me to Open in Excel with the normal user, even though I am in the admin version of SSMS.

Jon_Kirkwood

Ah that is interesting! I hadn't thought about that method of multiple SSMS instances. Very neat.

Hopefully the workaround fits into your process and that any future changes in SQL Prompt Excel functionality makes it more streamlined to save the output file without needing to open Excel too.

Thank you for the info, I'll keep that on file if I run into this situation again in the future.