The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
- Open SQL Management Studio
- Expand your database
- Expand the "Security" Folder
- Expand "Users"
- Right click the user (the one that's trying to perform the query) and select
Properties. - Select page
Membership. Make sure you uncheck
db_denydatareaderdb_denydatawriter
This should go without saying, but only grant the permissions to what the user needs. An easy lazy fix is to check db_owner like I have, but this is not the best security practice.
I think the problem is with the user having deny privileges. This error comes when the user which you have created does not have the sufficient privileges to access your tables in the database. Do grant the privilege to the user in order to get what you want.
GRANT the user specific permissions such as SELECT, INSERT, UPDATE and DELETE on tables in that database.
The syntax to grant select permission on a specific table :
USE YourDB;GRANT SELECT ON dbo.functionName TO UserName;To grant the select permission on all tables in the database:
USE YourDB;GRANT SELECT TO UserName;