Select permission was denied on object

Question

Well, I'm creating an app web with Cold Fusion, every time I debug my project in the browser I get this error:

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer] The SELECT permission was denied on the object 'users', database 'hotel', schema 'dbo'

I'm using a user called CFLogin and I grant to him select permissions through this command in SQL Server:

USE hotel
GO
GRANT SELECT ON dbo.users TO CFLogin

Also I've executed the follows stored procedures to give roles to my user:

EXEC sp_addrolemember 'db_owner',CFLogin
EXEC sp_addrolemember 'db_datareader',CFLogin
EXEC sp_addrolemember 'db_accessadmin',CFLogin

But it always results in the same message: Select permission was denied on object... I've even rebooted the service but nothing seems working at all.

What am I doing wrong?

Regards!

Answer 1

I also encountered a similiar issue while taking coldfusion enabled website in a Windows 2008 server running IIS 7. Executing the below queries helped resolve the issue for me,

use DATABASENAME
grant select on dbo.TABLENAME to public; 

Executing the above query from sql prompt helped resolving this issue for me. Replace DATABASENAME and TABLENAME with your sql server database and tablename which is shown in error.