Bonjour,
I have a weird problem here and I can't answer as to why it is as it is, but I surely have to do with it.

This said, I need to grant sso_role to sa but the problem is, no one in the database has the sso_role. Is there any way to fix this issue?

Thanks for your help.

The work around here may be below.
1. Take a backup of your sysloginroles table as below
bcp master..sysloginroles.out sysloginroles.out -U<User_Name> -P<Password> -c -t"~" -r"|"
2. execute below sql
insert into master..sysloginroles values(1,1,1)
go

3. Restart the ASE.

4. Now execute sp_displaylogin sa

and now your sa must have sso_role.

Hopefuly this should work, and please don't forgrt to let me know if it works.

hi ishu.cs
what sybase version of this workaround? tnx

Hi,
I tried the solution, what i had given to u, on my m/c. But unfortunately it didn't worked.
The reason is that, the sysloginroles table has insert permission to sso_role role only.
So it will not allow any user, not having sso_role, to insert in the table. bcp in will also not help.

So you need to try any of the below options.
1. Try to modify the sysprotects table to grant the insert permission on sysloginroles to sa, and then try to insert a row in sysloginroles(which will also not work most probably, as I have every doubt that, sysprotects also have insert permission to sso_role only).

2. If above works, then good, else you are left with only two choices.

a. if u have a backup of master(where sso_role was assigned to some login), load master database with that dump, keeping in mind the database alteration, device creation after the time of master database dump, will be lost and may cause to corruption( so a detailed planning is needed for this)

b. The other way which is easy to implement but is time consuming, is take the dump of all the user database, gather the information of all the devices, take the bcp out of syslogins,sysloginroles,sysdevices,sysdatabases and sysusages. Take the ddlgen of all the databases(create database) or save the o/p of sp_helpdb <user_db> for each user database.

Install a fresh ASE, create the devices, and user databases, load the user databases, from the dumps taken previously. Cautiously bcp in the sysloginroles, taken previously. precaution here is to do the bcp in, in such a manner, so that the system default roles and logins are not get effected, by your bcp in.

Thank you very much for your time and support ishu it is really appreciated. Fortunately for me, "allow update on system tables" was enable so I could put back the correct information in sysloginroles and fix the issue.

Fortunately because the solution I had in mine was something along your last suggestion which I did not want to do, for obvious reason :O)

Again, thanks for your help and hope this could of any use to someone else.

Best Regards

I am glad that my two cents were helpful.