Search code examples
sql-serverexcelpermissionspowerquerysql-view

With only the Select and View Definitions permission on a view, can SQL queries be sent from Excel without needing to type the query each time?

I have views that my users often only need to check for one particular person at a time. To do this, they do the following in Excel 365 desktop:

  1. Open a blank workbook
  2. Click on the 'Data' ribbon
  3. Click 'Get Data'
  4. Click 'From Database'
  5. Click 'From SQL Server Database'
  6. Fill in the 'Server' and 'Database' fields
  7. In the advanced options, type SELECT * FROM [VEIWS].[VIEW_NAME] WHERE [EMP.ID] = '123456'
  8. Click OK.

This is tedious for my users. If they want to check another person, they have to repeat the entire process. I'd love for them to just be able to use the query editor and change the only line that matters (see step 7), but they've only got the Select and View Definitions permission, which causes the query editor to complain. I'm afraid that I don't have the specific error message, but it's certainly to do with permissions.

Is there a less-repetitive way to do this from Excel? In an ideal world, I'd just make a sheet that lets them type in the EMP.ID immediately and then fetches the info. I think that it can be done with macros, but they're never my first choice and seem to require that I save passwords in the workbook.

Note that my users can't just fetch the entire view and filter it down in Excel. There are too many rows for Excel to handle.

Solution

  • I have no idea what permissions error you’re hitting, but people commonly use Windows credentials instead of Database credentials and get stuck. Power Query saves credentials on each computer, so you are relying on them signing in correctly. The first time someone connects to a data source, they are prompted for credentials. The default is for a Windows credential, and likely they need to enter a Database credential. If they get this wrong, they have to go into the Data Source settings to edit or clear the credential to fix it.

    As far as changing the value in the SQL, you can easily have a parameter in Excel that changes the EMP.ID value in your query. Ken Puls has a nice write up on the process here. Reply back if you’re stuck.