Unable to restore sa_role using sp_restore_system_role stored procedure

[DilipVoora]

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

[DilipVoora]

After including the role name in the stored procedure sp_aux_checkroleperm, I got the below while executing the sp_restore_system_role.

1> sp_restore_system_role sa_role
2> go
Msg 589, Level 16, State 1:
Server 'ASE2', Procedure 'sp_restore_system_role', Line 86:
Invalid role string received.
Msg 102, Level 15, State 181:
Server 'ASE2', Line 1:
Incorrect syntax near '='.
Restoring 'sa_role' in progress.
Msg 10353, Level 14, State 7:
Server 'ASE2', Procedure 'sp_restore_system_role', Line 395:
You must have any of the following role(s) to execute this command/procedure: 'sa_role' . Please contact a user with the appropriate role for help.
Msg 10334, Level 18, State 125:
Server 'ASE2', Procedure 'sp_restore_system_role', Line 395:
Permission related internal error was encountered. Unable to continue execution.
Msg 15967, Level 16, State 1:
Server 'ASE2', Procedure 'sp_restore_system_role', Line 395:
The 'GRANT' command failed when updating permissions to system roles in database 'sybsystemprocs'. The roles in the database may not have the correct permissions.
(return status = 0)

[former_member188958 · Accepted solution]

This procedure restores the default permissions to the specified role.
It does not re-create deleted roles.

To recover from deleting sa_role, you will need to load your most recent dump of the master database or reinstall the server.

-bret

[DilipVoora · Accepted solution]

Thanks for the clarification Bret.