BUG: OLAP Manager:Error Processing When Data Source and Server Use Integrated Security

• Microsoft SQL Server OLAP Services version 7.0

SYMPTOMS

The Microsoft OLAP Manager is unable to process a dimension when the underlying object(s) are owned by a user other than 'dbo' or the current account in which the MSSQLServerOLAPService service is configured to run. This occurs when the SQL Server data source uses integrated security. Database objects owned by the currently logged on user are available for use in building dimensions and cubes but return an error when processed.

The error messages returned during processing are:

ODBC error: Invalid object name '<source_table>'.;42S02; Time: <current datetime>
Invalid object name '<source_table>'.;42S02; Time: <current datetime>

CAUSE

This is caused because the OLAP Services uses the account of the currently logged on user to browse the list of objects available for building dimensions and cubes. However, when processing OLAP Services uses the account under which the MSSQLServerOLAPService service is configured to run. Because the object owner is not stored along with the object name, it cannot find objects owned by users other than the service account or 'dbo'.

WORKAROUND

Here are two workarounds for this issue:

• The first option is to change the owner of the tables to be dbo by using the stored procedure sp_changeobjectowner (for example, spchangeobjectowner table1, dbo). Once the object ownership has been changed, you must give permissions to the desired users. After permissions have been granted, the tables will show up to these users in OLAP Manager and dimensions built on these tables will process successfully.
  
  



• The second option is to change the security on SQL Server to use both SQL Server and Windows NT authentication and modify the library data source for the OLAP database to use a specific account and password. By doing this only objects owned by the dbo or the specified account used in the data source will be available to users building dimensions.

STATUS

Microsoft has confirmed this to be a problem in SQL Server OLAP Services version 7.0.

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbbug