search cancel

SQL data collection fails with error: <No information available for this COM error> or The EXECUTE permission was denied

book

Article ID: 224299

calendar_today

Updated On:

Products

Control Compliance Suite Control Compliance Suite Standards Server

Issue/Introduction

Control Compliance Suite (CCS)

When running a SQL data collection on a CCS SQL asset, it comes back with the following error(s):

SQLAsset\servername: <No information available for this COM error>

and/or

The EXECUTE permission was denied on the object 'xp_msver', database '<database_name>', schema 'sys'
(NOTE: The error message above may vary as various permissions listed in the resolution below may be missing)

Environment

Release : CCS 12.5.X and CCS 12.6 SQL data collection

Component : Window user account that CCS uses to collect data on SQL assets

Resolution

The Window user that CCS uses to collect the SQL data does not have the required permissions to collect the data.

Make sure the Window users account (that CCS is configured to use for SQL import\data collection) has the following required permissions on that server and SQL instance:

  • Make sure the user has the 'Log on as a batch job' permission (and is not in the Deny Log on as a batch job, as that will override the Log on as a batch job).
  • That the user is in the 'Administrators' group on that server.
  • Make sure user has read rights on db_datareader role on all databases.
  • Also make sure the user has all the sql permissions on Master as listed in the table below:
Dependent Tables (T)/Stored Procedure (SP) in SQL Server Master/Current Database Rights on the dependent database object
sp_Msdbuserpriv Master Execute
sp_MSdbuseraccess Master Execute
sysprocesses Master Select
sysusers Master Select
syslogins Master Select
xp_regread Master Execute
sysdevices Master Select
sysconfigures Master Select
syscurconfigs Master Select
syslanguages Master Select
sysservers Master Select
fn_helpcollations Master Select
xp_msver Master Execute
sp_helpsort Master Execute
xp_instance_regenumvalues Master Execute
xp_instance_regread Master Execute
sp_server_info Master Execute
xp_loginconfig Master Execute
sp_helpsrvrole Master Execute
xp_servicecontrol Master Execute

Additional Information

Please see a sample grant script that can be used as an example for you to create your own script to grant the Windows user privileges (user is also required to have read rights to the db_datareader role on all the databases) for CCS to be able to use to import the SQL assets and perform data collection on SQL asset(s).  Change 'CCSSQL_user' to match the user name that CCS uses in your environment.  

GRANT EXECUTE ON sp_Msdbuserpriv TO [CCSSQL_user]
GRANT EXECUTE ON sp_MSdbuseraccess TO [CCSSQL_user]
GRANT SELECT ON sysprocesses TO [CCSSQL_user]
GRANT SELECT ON sysusers TO [CCSSQL_user]
GRANT SELECT ON syslogins TO [CCSSQL_user]
GRANT EXECUTE ON xp_regread TO [CCSSQL_user]
GRANT SELECT ON sysdevices TO [CCSSQL_user]
GRANT SELECT ON sysconfigures TO [CCSSQL_user]
GRANT SELECT ON syscurconfigs TO [CCSSQL_user]
GRANT SELECT ON syslanguages TO [CCSSQL_user]
GRANT SELECT ON sysservers TO [CCSSQL_user]
GRANT SELECT ON fn_helpcollations TO [CCSSQL_user]
GRANT EXECUTE ON xp_msver TO [CCSSQL_user]
GRANT EXECUTE ON sp_helpsort TO [CCSSQL_user]
GRANT EXECUTE ON xp_instance_regenumvalues TO [CCSSQL_user]
GRANT EXECUTE ON xp_instance_regread TO [CCSSQL_user]
GRANT EXECUTE ON sp_server_info TO [CCSSQL_user]
GRANT EXECUTE ON xp_loginconfig TO [CCSSQL_user]
GRANT EXECUTE ON sp_helpsrvrole TO [CCSSQL_user]
GRANT EXECUTE ON xp_servicecontrol TO [CCSSQL_user]