Re: OPENQUERY - Access denied

starts the surface area tool.
Openquery is not authorized by default, you have to change the config to
allow it.


"Renato" <renato_buda@xxxxxxxxxxxxxx> wrote in message
news:1141698138.928526.215420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am unable to use OPENQUERY to a SQL 2005 OLAP server (RTM).
The actual query is very simple and works fine from management studio
on AS9.
I am a server administrator and logged directly onto server using
management studio against SQL2005 executing openquery against a linked
server to AS9 on same server.
A strange thing is that the profiler shows execution activity on AS9
and no errors. Also no errors appear in event viewer.

Here is the query:
SELECT *
FROM OPENQUERY(OLAP_SERVER, 'select [Measures].[Sales] on 0 from
sales')


Here is the error returned:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "OLAP_SERVER" reported
an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSOLAP" for
linked server "OLAP_SERVER".


Here is the script to create the linked server:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'OLAP_SERVER',
@srvproduct=N'MSOLAP', @provider=N'MSOLAP',
@datasrc=N'wrypgpvmw06.apac.pfizer.com', @catalog=N'SalesReporting'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'data
access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'rpc
out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER',
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'OLAP_SERVER', @optname=N'use
remote collation', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'OLAP_SERVER',
@locallogin = NULL , @useself = N'True'
GO



.

Relevant Pages

  • Re: String manipulation - taking one long csv string and putting into separate record fields
    ... From what I can work out, it looks as if the script is taking ... "%2147746132" from the computer VPN-01 when attempting to activate the ... computer VPN-01 when attempting to activate the server: ... EXEC xp_startmail ...
    (microsoft.public.sqlserver.programming)
  • Re: Replicating database schema changes in "daisy chain" situation
    ... create database central ... exec sp_replicationdboption 'central','merge publish','true' ... SQL Server B> is installed in another country and replicates key master data from server A.> MSDE Instance C replicates with Server B to receive this master data as well> as merge data that is shared only between B and C. ...
    (microsoft.public.sqlserver.replication)
  • Re: Script to delete backup files which are 7 days old
    ... you might want to know that the sp_smtp_sendmail and xp_smtp_sendmail have been used for this purpose by many many people on SQL Server 2000. ... DECLARE @DeleteFiles NVARCHAR ... SELECT 'exec xp_cmdshell ''DEL "' ...
    (microsoft.public.sqlserver.tools)
  • Re: Verwaiste Benutzer
    ... > jedoch fehlt die Zugriffsberechtigung auf den Server. ... > Muss ich jetzt tatsächlich allen Benutzern neuen Zugriff ... EXEC sp_configure 'show advanced options', ... WHERE name NOT IN (SELECT name FROM master.dbo.sysxlogins) ...
    (microsoft.public.de.sqlserver)
  • Re: Share Point Services
    ... Change the database ownership and permissions for the configuration database ... Microsoft SQL Server, and then click Query Analyzer. ... EXEC sp_changedbowner @AdminVSAccount; ...
    (microsoft.public.sharepoint.windowsservices)
Loading