If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > Grant sso_role without sso_role

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 6
Grant sso_role without sso_role

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2012
Posts: 70
hi ishu.cs
what sybase version of this workaround? tnx
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2012
Posts: 6
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2008
Posts: 95
I am glad that my two cents were helpful.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On