on 04-20-2020 5:23 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
77 | |
11 | |
8 | |
8 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.