Skip to Content
0
Apr 20, 2020 at 04:23 PM

Unable to restore sa_role using sp_restore_system_role stored procedure

58 Views

Hi All,

Are there any restrictions in using the stored procedure sp_restore_system_role? I have deleted the sa_role from my test system and when tried to restore it , I am getting the below error. (All my intention is to check the usage of the stored procedure.)

1> sp_restore_system_role sa_role
2> go
Msg 589, Level 16, State 1:
Server 'ASE2', Procedure 'sp_aux_checkroleperm', Line 69:
Invalid role string received.
Msg 589, Level 16, State 1:
Server 'ASE2', Procedure 'sp_restore_system_role', Line 85:
Invalid role string received.
(return status = 1)

So then I tried the below ways to get it fixed but nothing was successfull.
1. Logged in with a login having sso_role and executed the sp.
2. Dropped and recreated the stored procedure sp_restore_system_role.
3. Ran installmaster script
4. Included the role name in the stored procedure 'sp_aux_checkroleperm' as per the below. (Don't scold me if it is wrong as I ma very poor in debugging).

select @gp_enabled = a.value from master.dbo.syscurconfigs a,
master.dbo.sysconfigures b where
a.config = b.config and b. name like 'enable granular permissions'
if (@gp_enabled = 0 and @rolename is not NULL )

begin /* If @rolename is "dbo" then we just check if user is dbo. */
if (@rolename = "dbo" or @rolename="sa_role" or @rolename = "DBO")
begin
if (user_id() = 1
) return 0 else return 1 end select @dummy = has_role(@rolename,1) if (@dummy = 0)
begin
return 1

end

end

1> select @@version
2> go
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------Adaptive Server Enterprise/16.0/EBF 22383 SMP/P/x86_64/Enterprise Linux/asecepheus/3530/64-bit/FBO/Sun Feb 16 06:09:40 2014
(1 row affected)

Please guide me on this to restore the deleted role.

Regards,
Dilip Voora