Permission Error again

I am using an ERP system with SQL Server as the back end. When our user tried to run a function in ERP system, they got the following error:

229,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'Grant', database 'HEI', owner 'dbo'

I ran "sp_helprotect Grant" to check the permission, it looks okay.

When I used the administrator's User ID to login to the machine and ran the same function in ERP, it works fine.

Anyone knows how to fix it?

The error means that the principal used by your system to connect to SQL Server and SELECT from object [Grant] doesn’t have permission to select on that object.

When you connect as an administrator and try to select from the same object, the system will use your current context (administrator) and allow the call, but most likely (and actually I would recommend against it) your ERP system is not using administrator credentials and it requires explicit permissions in order to execute the SELECT statement.

Can you tell us what was the result of the sp_helprpotect call? It should be similar to this output:

OwnerObjectGranteeGrantor ProtectType ActionColumn

-- -- - -- --

dboGrant<<ERP user>> dboGrantSelect .

As an additional note: choosing an object name that may conflict with SQL Server syntax (such as [Grant]) is not a practice I would personally recommend as it may cause conflicts when parsing the statements (forcing you to always use the quoted [] name form) and making the statements a lot more difficult to read.

I hope this information helps

-Raul Garcia

SDE/T

SQL Server Engine