SELECT permission denied on object ‘USERS’, database ‘qcsiteadmin_db’, owner ‘dbo’

Steve Stonebraker
September 30, 2010

How to resolve this error:

[Mercury][SQLServer JDBC Driver][SQLServer]SELECT permission denied on object 'USERS', database 'qcsiteadmin_db', owner 'dbo'.;

Stack Trace:
java.sql.SQLException: [Mercury][SQLServer JDBC Driver][SQLServer]SELECT permission denied on object 'USERS', database 'qcsiteadmin_db', owner 'dbo'.
at com.mercury.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.mercury.jdbc.base.BaseExceptions.getException(Unknown Source)

The reason this error is occurring is because user td does not have rights to the tables in your qc admin table. This table is sometimes labeled ‘MercuryQC_SA_DB’.

Depending on your database you may need to replace the name right after “Use”:

use MercuryQC_SA_DB
GRANT ALL ON td.LOGIN_SESSIONS to td
GRANT ALL ON td.PARAMS to td
GRANT ALL ON td.PROJECT_SESSIONS to td
GRANT ALL ON td.PROJECTS to td
GRANT ALL ON td.SEQUENCES to td
GRANT ALL ON td.SESSION_LICENSE to td
GRANT ALL ON td.SESSIONS_HISTORY to td
GRANT ALL ON td.SESSION_LICENSE_HISTORY to td
GRANT ALL ON td.APPSERVERS to td
GRANT ALL ON td.USERS to td
GRANT ALL ON td.CACHE to td
GRANT ALL ON td.USERS_PROJECTS to td
GRANT ALL ON td.USERS_PROJECTS_TEMP to td
GRANT ALL ON td.MESSAGES to td
GRANT ALL ON td.GROUPS to td
GRANT ALL ON td.REPOSITORY to td
GRANT ALL ON td.ROLES to td
GRANT ALL ON td.USERS_GROUPS to td
GRANT ALL ON td.USERS_ROLES to td
GRANT ALL ON td.CLUSTERED_RESOURCE to td
GRANT ALL ON td.ADMIN to td
GRANT ALL ON td.AUDIT_LOG to td
GRANT ALL ON td.AUDIT_PROPERTIES to td
GRANT ALL ON td.DBSERVERS to td
GRANT ALL ON td.DOMAINS to td
GRANT ALL ON td.LOCKS to td
GO

And How did we get all those table names? By using this query:

use MercuryQC_SA_DB
go
select 'grant all on' + name + 'to td'
from sysobjects
where type = 'U'
go
Tags: , , , , , , ,