cancel
Showing results for 
Search instead for 
Did you mean: 

DB Connect to MSQL database - authorisation ?

Former Member
0 Kudos

Hello

I like to extract data from MSS system (our BW is 3.1). Thus I followed all advices (OSS, SDN network) like upper case, user-own table ect. However I am not able to get a proper connection. Thus I am wondering if someone has an idea for the following:

In the 'DB connect: Table and view overview', I can only see the table of the MSS database owner. Unfortunately the view built specifically by the extraction-user doesn’t appear in the 'DB connect Table and view overview'.

Thus has someone an idea, which authorisation is required for the extraction user in MSS at least to get this view in 'DB connect Table and view overview'.

Anyhow it would be excellent if someone could provide an extract of the given authorisation to his user, which is able to extract successfully data.

Best regards

Christian

PS: Through EXCEL /ODBC using same user and view the data extraction is successful.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks Anil

But... I am able to extract the view content in a excel sheet using import external data ODC (simply using data connect wizard). I am using the same user and I am able to select the extraction-user own table.

But what I dont understand is that in SAP BW DB connect I even cant select that table.

So do you have an idea about the difference?

best regards

Christian

PS the OSS note about login and roles has been applied

Former Member
0 Kudos

Hi Christian,

DB Connect and the excel external data source provider are not communicating with SQL the same way.

I would follow OSS note 512739,as Anil suggested, on how to set up DBConnect for MS SQL. This is what we have done, and got it working with no issues.

Cheers! Bill

Message was edited by: Bill Faison

Former Member
0 Kudos

Hi,

Make sure that the owner of the view (created by the user 'extraction-user') is equl to 'extraction-user'. I am asking this because, even though view is created by extraction-user,the owner may be different.

It happend when I was extracting data from view using external database .

With rgds,

Anil Kumar Sharma .P

Message was edited by: Anil Kumar Sharma

Former Member
0 Kudos

Hello Anil

Thanks for the fast reaction;

The owner of the view is the same as the 'extraction-user'.

- do think the user has to be written in capital letters? relevant?

However I think it has to do something with authorisation. At least the desired view isnt shown in 'DBconnect: Table and View overview', whereas the view of the MSS database owner are listed.

On the other hand I am able to extract data into Excel using ODBC. Thus I suppose SAP BW has some additional authorisation/formal checks.

As I followed all formal advices, so authorisation could be the blocking factor.

However have you a list/example of the rights you have given to your extraction user in MSS.

Best regards

Christian

Message was edited by: christian baumann

Former Member
0 Kudos

Hi,

<i>have you a list/example of the rights you have given to your extraction user in MSS</i>

Right now i donot remember all those authorization relevent information.

But i rember that this was solved somthing relevent to owner issue.

as you said, <i>do think the user has to be written in capital letters? relevant?</i>, check with capitals.

With regds,

Anil Kumar Sharma .P

Former Member
0 Kudos

Hi,

Take alook on the following note 512739.

As it said in the point d)

1) Only the DB objects (tables and views), which directly belong to the database user who logged on, are visible.

<i>A login belonging to the sysadmin server role (for example, 'sa') must not be used.</i>

2) <i>You should add this login to the db_ddladmin database role in the corresponding database. This generates a similar DB user at database level. The user you use must have the 'CREATE VIEW' authorization at least. This is contained in the mentioned role.</i>

Also make sure that you have selected capital letters in view name.

With rgds,

Anil Kumar Sharma .P