Database permissions for Power Query (Excel Get and Transform)

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  January 22, 2019 at 7:57 am

  #382606

  Hi Guys,

  A little off topic.  I am trying to demonstrate to the organisation how to set up data access securely (rather than use the SA account everywhere!) and I am hitting a bit of a blocker.
   I have a stored procedure (no parameters) and returns a data set after writing interim results to #temp tables.  I created this stored procedure using the SA account

  The network administrator has created a new AD group for me and one other AD account and added the group to the instance permissions with public and datareader access to the database.
  When I log in to management studio I can run the stored procedure

   I have created a DSN (shared 32/64bit) on my windows 10 machine for the database and specified windows credentials.  I can successfully connect to the database in the DSN test
  When I try to create a new data source in Excel I get the error message
  The EXECUTE permission was denied on the object 'Stored_Proc_name', database 'DatabaseName', schema 'dbo'."
  I get this whether I try to make the connection using
  Get Data -> From Database -> From SQL Server Database (which asks for the server instance and database name)
  Get Data -> From Other Sources -> From ODBC (which asks for the DSN)
  Get Data -> From Other Sources -> From Microsoft Query (and then follow the wizard and manually edit the SQL in the last step)

   I suppose that we should be thankful that the behaviour is consistent but why can I run the procedure when logged in to management studio, but not from Excel: I expect I would get the same error using Access or PowerBI

  The preferred method would be to use the DSN as we then have to
  A) set up the connection on the user's machine/virtual desktop and 
  B) add them to the AD group.