http://goo.gl/EmKxy0

Syssrvroles dropped

December 12th, 2009 Leave a comment Go to comments

Hey Guys!
In one case, I dropped out the syssrvroles accidently. Now, no one having any roles, even sa is not having its roles..I hav backup of all system tables and dump of master db. But We dont hav sso_role for any login we can’t do the bcp in , we can’t insert any row in any system table. Even we can’t change the allow update on system table, requires sso_role. Anyway. We have backup of master..so we can recover it..by loading the dump of master , but still need suggestions….

Check the Sybase Wiki @ sybasewiki.com
  1. January 3rd, 2010 at 19:04 | #1

    Let say you have 2 sa connections to your dataserver. You delete syssrvroles and then exit from one sa connection: you still have one remaining sa connection.
    You realize you deleted by accident syssrvroles, then you can still use the remaining sa connection to manually insert the missing rows in syssrvroles (with insert/select statements). It works because roles are applied at connection time and roles are kept in memory as long as you are logged in with the same connection.

    When I work manually and directly on system tables (especially on syssrvroles to delete/update), I always connect to dataservers with 2 sa connections. This way I can still do something to avoid to reload master database.

    But keep in mind the BEST PRACTISE when working on system tables: always start your update/delete/insert statement with BEGIN TRAN 😉 You can always issue ROLLBACK if something goes wrong.

  2. December 19th, 2009 at 08:49 | #2

    Yeah..through master db backup we can recover it.
    I m not sure how can we insert the rows in syssrvroles, (with the connection open prior to delete), because now we have no roles for sa. Have you checked it? So after the syssrvroles delete, prior sa connection will not be check for current roles, it has ..Right?

  3. December 18th, 2009 at 18:26 | #3

    You had a master database backup: good for you !!
    Indeed when you delete syssrvroles, you have no other choice to load the master database backup… because every logins lost their roles (including sa).
    If you are a lucky guy, you may rebuild syssrvroles with insert commands only if you left a sa connection open prior to delete syssrvroles. (It happens to me one time…)

  1. No trackbacks yet.