When you downgrade Adaptive Server to version 15.5, Adaptive Server truncates and locks role passwords. In addition, Adaptive Server does not support the use of allow password downgrade for role passwords.After a downgrade, the administrator should reset the role passwords and unlock the role accounts before using them again.
During the downgrade process, Adaptive Server:
Truncates role passwords and locks roles
Removes any attributes in sysattributes under class 35, as well as class 35 itself
Removes locksuid, lockreason, and lockdate columns from syssrvroles
The actions to downgrade a password occur when you execute sp_downgrade. in single-user mode. A dataserver started with a “-m” command line option starts the server in single-user mode and allows only the system administrator to log in.
In this example, executing sp_downgrade results in the password of the “doctor_role” role becoming locked and truncated. The administrator can redirect this output to a file so that the passwords for these roles can be reset:
1> sp_downgrade 'downgrade','15.5',1 2> go
Downgrade from 15.7.0.0 to 15.5.0.0 (command: 'downgrade')
Checking databases for downgrade readiness.
There are no errors which involve encrypted columns.
Executing downgrade step 2 [dbcc markprocs(@dbid)] for :
- Database: master (dbid: 1)
sql comman is: dbcc markprocs(@dbid)
...
Executing downgrade step 26 [delete statistics syssrvroles(password) if exists (select 1
from syssrvroles where password is not
null) begin print "Truncating password and locking following role(s)" select name from
syssrvroles where password is not null update
syssrvroles set password = null, status = (status | @lockrole) where password is not null
end update syscolumns set length = 30
where id = object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid
= null, lockreason = null, lockdate = null
where locksuid is not null or lockreason is not null or lockdate is not null delete
syscolumns where id = object_id('syssrvroles')
and name in ('locksuid', 'lockreason', 'lockdate')] for :
- Database: master (dbid: 1)
sql comman is: delete statistics syssrvroles(password) if exists (select 1 from
syssrvroles where password is not null) begin print
"Truncating password and locking following role(s)" select name from syssrvroles where
password is not null update syssrvroles set
password = null, status = (status | @lockrole) where password is not null end update
syscolumns set length = 30 where id =
object_id('syssrvroles') and name = 'password' update syssrvroles set locksuid = null,
lockreason = null, lockdate = null where
locksuid is not null or lockreason is not null or lockdate is not null delete syscolumns
where id = object_id('syssrvroles') and
name in ('locksuid', 'lockreason', 'lockdate')
Truncating password and locking following role(s)
name
------------------------------------------------------------
doctor_role
Executing downgrade step 27 [delete sysattributes where class = 35 delete sysattributes
where class = 39 update syslogins set lpid =
null, crsuid = null where lpid is not null or crsuid is not null delete syscolumns where
id = object_id('syslogins') and name in
('lpid', 'crsuid') delete syslogins where (status & @lp_status) = @lp_status update
syslogins set status = status & ~(@exempt_lock)
where (status & @exempt_lock) = @exempt_lock] for :
- Database: master (dbid: 1)
sql comman is: delete sysattributes where class = 35 delete sysattributes where class =
39 update syslogins set lpid = null, crsuid
= null where lpid is not null or crsuid is not null delete syscolumns where id =
object_id('syslogins') and name in ('lpid',
'crsuid') delete syslogins where (status & @lp_status) = @lp_status update syslogins set
status = status & ~(@exempt_lock) where
(status & @exempt_lock) = @exempt_lock
...
(return status = 0)
Additional messages appear in the error log to identify steps that occurred during sp_downgrade and any system errors that may occur, such as in this example of error log output for the example downgrade procedure:
00:0006:00000:00006:2011/06/28 06:21:23.95 server Preparing ASE downgrade from 15.7.0.0 to 15.5.0.0. 00:0006:00000:00006:2011/06/28 06:21:24.12 server Starting downgrading ASE. 00:0006:00000:00006:2011/06/28 06:21:24.12 server Downgrade : Marking stored procedures to be recreated from text. 00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing full logging modes from sysattributes. 00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Downgrading data-only locked table rows. 00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing full logging modes from sysattributes. 00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing column sysoptions.number. 00:0006:00000:00006:2011/06/28 06:21:26.13 server Downgrade : Removing srvprincipal column from sysservers system table 00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Removing 'automatic master key access' configuration parameter. 00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Removing DualControl sysattribute rows 00:0006:00000:00006:2011/06/28 06:21:26.14 server Downgrade : Downgrading sysattributes system table. 00:0006:00000:00006:2011/06/28 06:21:26.16 server Downgrade : Downgrading syscomments system table. 00:0006:00000:00006:2011/06/28 06:21:26.19 server Downgrade : Truncated role password, locked role and removed columns locksuid, lockreason, lockdate from syssrvroles 00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Removing catalog changes for RSA Keypair Regeneration Period and Login Profile 00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Turning on database downgrade indicator. 00:0006:00000:00006:2011/06/28 06:21:26.21 server Downgrade : Resetting database version indicator. 00:0006:00000:00006:2011/06/28 06:21:26.21 server ASE downgrade completed.
After running sp_downgrade, shut down the server to avoid new logins or other actions that may modify data or system catalogs.
If you restart Adaptive Server at version 15.7:
After successfully executing sp_downgrade and shutting down the server, Adaptive Server performs internal upgrade actions again, and any changes to system tables are upgraded to version 15.7.
Before starting an earlier version of Adaptive Server to which you are reverting, you must execute sp_downgrade again.
You can enable locked roles and truncated password. In this example, the output of sp_displayroles shows that the downgrade process has locked “doctor_role” and truncated its password:
select srid,status,name,password from syssrvroles go
suid status name password ------ ------- ------------------- ----- 33 2 doctor_role NULL
This unlocks the role:
alter role doctor_role unlock
This sets a new password for the role:
alter role doctor_role add passwd "dProle1"
Running sp_displayroles now displays that the role is unlocked and has a password:
select srid,status,name, "vers"=substring(password,2,1) from syssrvroles go
suid status name vers ------ ------- ------------------- ----- 33 0 doctor_role 0x05
