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!

It would appear that you're connected under a different login - not CFLogin — Adam Wenger
I'm connected with CFLogin and that's select works, but not my select pointing to users table — Enot

Reynold Joseph Reynold Joseph · Accepted Answer · 2013-06-27T13:57:50

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.

Select permission was denied on object

1 Answers