SQL – The SELECT permission was denied [FIXED]

Recently I had to solve an issue with a users permissions to Microsoft SQL. When running a report from Microsoft Excel, they would get the message;

It was a bit of an odd issue as the user was in a Active Directory group that would give them reader access to the database, and the message being returned by MS SQL was not particularly helpful to find the issue.

The Fix

While the user was in a AD group that would have given the permissions they required. The user and also been given the permission “db_denydatareader” directly on the database.

As a deny overrules an allow permission the user was denied access, causing the above message.

If you have the same issue, make sure the user does in fact have permission. Also make sure there isn’t a deny permission somewhere revoking their access. You can use this post from Microsoft to find the effective permissions for a user.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/determining-effective-database-engine-permissions?view=sql-server-ver16