I love the way SAP designed the data model for the SAP Data Services database. As you can read in this blog the tables and fields are quite easy to understand and the relation between tables is quite clear. I’m telling you this because there are times when you have to modify data without using SAP Data Services administration or client. During these years working with SAP Data Services I found some weird problems that I solved modifying data directly in the database. Don’t get me wrong, this is not the way to solve problems and it should be used as last resort.

Some examples of these problems are related to job executions or object attributes. For example, when you cancel an executing job in SAP Data Services there are times when the job appears in the administration tool as it is running. Even if there is no al_jobserver / al_engine running in the OS the job still appears as it is running. The only way to change the status is to modify the table AL_HISTORY and change the STATUS field for the job execution.

In this post I want to give you another example that I found lately. I didn’t find any information related to this on Internet so I hope it helps you!

SAP Data Service objects in read-only mode

When you check-in objects into the Central Repository from the Local Repository it is possible to change the permissions. You can set full or read permission for an object. This will mean that an user group will be able to modify or not the object in the Central Repository. It is also possible to create multiple groups so you can add users and permissions to that groups. The behavior is the following one:

• If the permission is full then the users in that group will be able to check-in a new version of the object.
• If the permission is read then the users in that group will be able only to see the object and download it from the Central Repository.

The problem here is if you have only 1 user’s group and some objects are in read mode. Then you won’t be able to check-in new versions of the objects or change the permissions. In other words, the object is completely lock and you cannot modify it since it is not possible to check-in the object from the Local Repository.

In the following image you can see 2 objects with read permissions:

If you try to modify the permission then the option will appear on gray and it will be no possible to select it. The same occurs when trying to check-in the object. If you try to change the permissions for the whole parent object (for example a data store when working with a table) then it will show the following message:

I look for the error message Permission denied. Current user has <Read> permission on this object, which is not sufficient to perform this operation (BODI-1116530) but I couldn’t find a way to fix the problem. So the next thing to do was doing a database profile and check what I can use…

Database Profile for SAP Data Services

The database we use for SAP Data Services is MSSQL Server. This product has an application named SQL Server Profiler which basically capture the executed transactions on the database. I enable the profiler and I tried to change the permissions for the datastore. It seemed that SAP Data Services executed a SELECT to the AL_OBJ_PERMS table in the database of the Central Repository. I executed that query and I found that there was no records in the table.

After this I checked the same query without filtering by name:

Let me explain it a bit:

• OBJECT_TYPE: Quite easy to understand. The values could be 0 (Job or Workflow), 1 (Dataflow), 3 (ABAP Transforms), 5 (Datastores), 7 (Datastore Tables), etc.
• GROUP_ID: The ID for the user’s group. In this case we only have one group so the ID will be 1.
• PERMISSION: The permissions for the object. In this case 2 means full permissions.

So you probably will think that if 2 means full permissions then 1 will mean read permissions, right? No, read permissions means that you don’t have any data in the table. So if you have an object in the Central Repository with read permission and you search for it in this table then you will find no entries in the table related to that object!

Giving full permissions at database level

Once you understand this it is really easy to give full permissions to the object. Actually the only thing you need to do is to insert an entry in the database with the following values:

Please notice that the values 7, 1 and 2 are related to the fields I explained in the last point. In this case I give full permissions to the object OBJECT_NAME for the group 1. Another important thing to know is that the name of the datastore tables at database level is a little bit weird. The name is a combination of the datastore plus the table name: DATASTORETABLENAME. Please check first the name before inserting any entries in the database.