EXECUTE permission was denied in sysdb database, schema 'ssma_oracle'.

Question

Hello,

I have migrated a database from oracle to SQL server.

After migration when I run a script on the migrated database,

I get following error . The user with which I have migrated the database and I am running this script are same.

Also I have given these 3 permissions on sysdb database to this user - public, db_datareader db_datawriter.

Please let me know what am I missing?

Msg 229, Level 14, State 5, Server SERVER5009\SQLEXPRESS, Procedure PROC_LOADTEST , Line 303
The EXECUTE permission was denied on the object 'db_error_get_oracle_exception_i
d', database 'sysdb', schema 'ssma_oracle'.
Msg 229, Level 14, State 5, Server SERVER5009\SQLEXPRESS, Procedure ssma_rethr
owerror, Line 1
The EXECUTE permission was denied on the object 'ssma_rethrowerror', database 's
ysdb', schema 'ssma_oracle'.

Thanks a lot.

Answer 1

Try:

USE sysdb
GO
GRANT EXECUTE ON PROC_LOADTEST TO user
GO
GRANT EXECUTE ON ssma_rethrowerror TO user

Refer:

http://mssqlfun.com/2013/01/09/the-execute-permission-was-denied-on-the-object-sp_start_job-database-msdb-schema-dbo/

Answer 2

Hi dave_gona,

I executed the above commands and run the procedure again.

Then I got this message -

Msg 59999, Level 16, State 1, Procedure PROC_LOADTEST, Line 324
SSMA Oracle exception emulation for [ORA-00001]

Please suggest.

Answer 3

This is not looking SQL issue, looks like something to do with oracle.

Answer 4

Hi ManiC24,

Msg 59999, Level 16, State 1, Procedure PROC_LOADTEST, Line 324
SSMA Oracle exception emulation for [ORA-00001]

As other post, the issue is related to Oracle. The number 59999 is used for all Oracle system, user-defined, or predefined exceptions.

Based on my research, ORA-00001 is common when: "a program tries to insert a "duplicate" row in a table,"  and that, "when a unique constraint is violated the row is NOT added to the table. To overcome this error, either remove the unique restriction or do not insert duplicate key.

Reference:
http://www.dba-oracle.com/sf_ora_00001_unique_constraint_violated.htm

Thanks,
Lydia Zhang

---

Lydia Zhang
TechNet Community Support