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....
You had a master database backup: good for you !!
ReplyDeleteIndeed 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...)
Yeah..through master db backup we can recover it.
ReplyDeleteI 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?
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.
ReplyDeleteYou 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.