Search code examples
sql-serverexcelvbasspi

Integrated Security SSPI - The Select Permission was denied on the object

I am getting this error for 2 of 3 users when they attempt to run a Macro enables Excel workbook that queries MSSQL 2014 from Excel 2013 & 2016. I thought since the code had "integrated security = SSPI" set, that every user would have the same access. I don't want to grant access to individual users, since this is a workbook with VBA macros that will be used department-wide.

enter image description here

enter image description here

What am I doing wrong? How do I force the code to use a central user?

Solution

  • Specifying integrated security = SSPI means you're going to manage permissions in the DB on a per user basis (ideally through roles instead of assigning permissions directly to a user, which really makes things much easier but still more work if you don't know who will be using your file at any time).

    If you want to use a dedicated user (preferably a readonly user if connecting from Excel, and possibly only readonly on the necessary tables) you can use the connection string format of 

    Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

    This means that you'll distribute a password with your Excel file. Maybe you're ok with that, maybe not.