The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
I moved a database from SQL Server 2012 to Azure. I don't want to use the user master, so I created a user test. This is what I did for database XXX on Azure:
create user test from login test with default_schema=[dbo]
exec sp_addrolemember 'db_owner','test'
I checked, and the database objects I am interested in are all in the schema dbo. The table Users is in the schema dbo.
The connection string in my web project has test as the login. It produces the error message:
The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
What does the error message mean and what can I do to let user test access the database XXX?
To solve the select permission was denied on the object error you can Check this syntax for granting the select permission:
USE YourDB;
GRANT SELECT ON dbo.functionName TO UserName;