sysobjects UID changed in v17

[former_SQLA_member1694879]

We are in the process of upgrading from v11 to v17 (if nothing else to get away from 'all threads are blocked' messages) and noticed that a utility to update tables based on the content in another DB/table suddenly stopped working

In v11 we could return a full list of all the tables we created (not system ones) using

SELECT Name FROM sysobjects WHERE UID = 7

and loop though them adding columns that might not be there..

Now, it seems, the UID for these is 1 - is there a reason for this change?

A big thank you to Graeme (this forum) who put in a good few hours today to fix an issue where I could not login anymore - with the total lack of any support from SAP I was beginning to think we would have to move to MS SQL

[MarkCulp · Accepted solution]

I do not know the reason why your UID has changed from 7 to 1 - I'm not even sure what user UID 7 would have been? - but I would say that you should never depend on hard coded numbers (in this case UIDs) in your scripts. Rather you should always lookup the UID from the SYSUSERS view (or lookup user_id in SYSUSER table) using the name of the user, in this case DBA.

HTH

[former_SQLA_member1694879 · Accepted solution]

Well there you go!

In v11 sysusers returns 8 rows (DBA is 101 and my name is 7)

In v17 sysusers returns 130 rows (DBA is 101 and my name is 1)

Thank you

[Former Member · Accepted solution]

dba in all of the demo databases supplied since version 10.0.0 have user_id/uid == 1. I believe that has always true for newly created database for over 2 decades.

It would seem you may have coded to a historical dependency you may have created somehow. If you have ever dropped your pre-existing dba account and later re-added it, you could have created this situation quite easily that way. It may have been something you done back in the 9.0.x or earlier days; that V10 dbunload (maybe) did not handle/clean-up correctly.

At that point you would run the risk of dbunload renumbering it on you. {back to Mark's caveat on that} This could something I would expect from more recent versions of SQL Anywhere.

[former_SQLA_member1694879 · Accepted solution]

When we create a new database CREATE DATABASE path\\name the user (myself) is also created DBA User Name Then START DATABASE Then change the default DBA password GRANT CONNECT TO DBA IDENTIFIED BY NewPassword

Yes we did upgrade from v9 to v11 - it was my bad not figuring out that I could refer back to sysusers to get the UID - even ol' farts like me can learn something new all the time 🙂