|
I'm curious if anybody has some information on migrating a database from SQL Anywhere 12 to SQL Anywhere 17 when it comes to the new roles feature that was implemented in SQL Anywhere 16. enter code here First off, I unloaded the full database and schema out of our version 12 database (12.0.1.3942) Opened SQL Anywhere 17.0.4.2053 No active connections in SQL Central.
Now comes the tricky part. Choose the Security Model for the system procedures Based on my guess, because my database is coming from SQL Anywhere 12, I need to select the second option, to execute the system procedures as the definer. Finally I give it the server name and the database name, and tell it to open the database after created. Once in the new database in 17, I want to select the reload script that was created when I unloaded my SQL Anywhere 12 database. READ "C:\Users\Jeff Gibson\Documents\Extraction\reload.sql" Immediately at line 52, I hit the following error... Could not execute statement. Invalid user ID or role name 'dbo' specified SQLCODE=-1536, ODBC 3 State="HY000" C:\Users\Jeff Gibson\Documents\Extraction\reload.sql:52 You can continue executing or stop. GRANT CONNECT,DBA,GROUP,RESOURCE TO "dbo" AT 3 From there I just hit stop. Because I'm not sure if there is something critical that needs to be handled with what was the old dbo system user. I'm not even sure I have my brain totally wrapped around roles yet. But from what I can tell, it looks like the dbo system user was moved into roles. Is this an error/warning that I can skip? Since dbo gets created under roles? Or do I need to modify something so the user gets handled correctly on the creation of the SQL Anywhere 17 database. Any information on this would be greatly appreciated! Thanks in advance for your help! Jeff Gibson |
|
This issue is caused by unloading using v12 software. That creates a reload script that is valid for v12 but may not be for other versions.You should use the v17 unload tool to create the unload script. Or as Reimer has suggested directly unloading into a new database. In v16 and later, the statement: GRANT <...> TO "<user>" is only valid for an USER and "dbo" is a System Role not an user. You mean "is only valid for an user", right?
(06 Apr '18, 14:51)
Volker Barth
Correct. The statement is valid only for an user.
(09 Apr '18, 19:13)
Chris Keating
|
|
To be honest, I didn't look that much into new features, when we started converting client databases from SA12 to SA17. So we trusted in the frequently cited Watcom rule and just did an unload into a new database. dbunload -v -c "<connection string for old db>" -an "<path for new db" -ap 4096 -ea None -ii The defaults worked perfectly, so we continued using this approach. The only downside: I don't have any advice for your specific question |
|
We have also migrated several databases from 12.0.1 to v16 and v17 without issues, including replicated databases, generally via dbunload. So we still use the old privilege system (with a few adaptions). We also still use dbo as schema for most user tables, and that was no problem during the migration. In your case, do you also connect as dbo (i.e. with its own credentials) or do you only use that to (say, via SETUSER) to create unqualified database objects? 1
In short: Using DBUNLOAD with one of -an or -ar should prevent you from answering all those questions asked by SQL Central...
(06 Apr '18, 09:53)
Volker Barth
|
