SAP Database Schema owner is not correct

[Former Member]

I have a problem where we migrated our DB from 2000 to 2005. Unfortunately the MCOD option was selected and then later canceled, but still resulted in changing the DB owner from 'dbo' to 'ebp' only in the database data system confguration maintenance entry. since this option was canceled the DB objects owner still remains dbo.(table, sp...)( and this is okay) . But when going to DBA Cockpit in DB13 system configuration it says the Schema\owner is 'ebp' and not 'dbo'. The question I have is how can I get this changed back to dbo? The system runs fine, but the DB01,Db02 transactions all fail because it says it can't find the stored procedure 'ebp.<object name>'. I know if I can get the owner name changed back to dbo, everything will be back to normal.

My inital thought is to change a single table where this value is stored on database data owner name, but I can't find the entry. If that is not suitable then I can go into Db13 and look at system configuration and select the single entry for the DB and delete the local entry and re-receate the entry, but am afraid this may not work either.

Help please?

[Former Member · Accepted solution]

Hi,

If on windows also check your envoirement variables for SIDadm and SAPSERVICESID. Check that no MSSQL_SCHEMA variable is set to ebp. If it is delete them.

If necessary also remove value HKEY_LOCAL_MACHINE\SOFTWARE\SAP\<SID>\Environment\MSSQL_SCHEMA from the registry.

All in note 551915.

Regards

[Former Member · Accepted solution]

Thanks to the community!!! I got my issue mostly resolved by using the MSSQL conversion tool. I awarded point to those that responded and helped out. The recent post with Note 551915 really helped clear-up how to use the tool and set the system from MCOD to non-MCOD by taking out some of the default profile info, but I havenu2019t tried it yet. I wish I had found this note prior to this weekend because my original goal was to change from a partial MCOD system to a non-mcod system. Oh well.

Some follow-up background on running the tool this past weekend that may be helpful...

When I ran this tool over the weekend it wasn't perfect. It ran through and updated my entire DB object (Tables & Views) to the instance specific (MCOD ID) 'ebp'. (I wish I would be prompted if I want to switch to MCOD or switch to non-mcod system.)The MCOD step 1& 2 went okay and updated all 20,000+ tables and views (and deleted 15 too!!!! I am hoping those were unused tables). However, when the process got to the step 3 to update the stored procedures and functions the process errored out. I could never figure out why it errored out and I never got through the full MCOD wizard. What I did notice is the MCOD script actually deleted all of my SP's and table functions (Probably part of the changing of schema process). So I was left with a DB with out SP's and that is really bad. To fix this problem I manually copied the SP's and table functions from another SRM DB to the new 'ebp' schema and got the system up and running. Now Db13, ST04, Db01, DB02 all are working without errors. I am not exactly excited I didn't get through the MCOD script because I am not sure what else I may have missed. I plan to explore this tool more, on a test system, and try to switch from MCOD to non-MCOD system.

If some one could also help clarify what the remaining steps of the MCOD process do it would help me figure out what I may have missed past the stored procedure conversion steps?

Thanks again for everyoneu2019s assistance.

[former_member211576]

Hello Scott,

When you upgrade, you need to use SAP tools for MSSQL to correct the schema owner issue. Please search note for "SAP tools for MSSQL".

[clas_hortien]

Hello,

the problem is, that you have a SAP profile parameter

dbs/mss/schema = edp

set in your default profile. Remove it or change it to dbo and restart the SAP System. You should do this as soon as possible as you currently run in a not recommended scenario. All new tables and other objects will be created in the edp schema currently, as long as you don't change this.

This is how it works:

- the SAP system runs as edp schema

- but the objects the system is locking for (edp.TABLENAME) are not there, as they belong to dbo

- on the SQL Server the request for a edp table is fullfilled with the dbo table with the same name. This only works, as you have a dbo system. On any other schema system this will fail.

- when you create a new object, this will be created in the edp schema, as the SAP system will specify the schema explicitly during the creation.

Best regards

Clas

[Former Member]

Clas,

Thanks for the reply. Yes you are correct, my schema on the default profile parameter was incorrect. I thought I had checked it, but it had the wrong owner name. However after restarting the system the schema owner is still the wrong one. (Currently I am still amazed my system even starts). I will check into what Dennis mentions on the SAP tools to see if I can resolve it that way.

Thanks,

Scott

[Former Member]

Scott,

You need to run the SAP Tools for MSSQL. This will go through and convert the schema to the correct one.

You can find the download at [https://websmp210.sap-ag.de/msplatforms] and select MS SQL Server.