Originally, my application can change application role by command.
exec sp_setapprole 'ARole', {ENCRYPT N'PASS01'}
And after I changed the application role's password to 'PASS02', I found
exec sp_setapprole 'ARole', {ENCRYPT N'PASS02'}
wouldn't work and returns an incorrect password.
I found it works after I changed the code to
1) exec sp_setapprole 'ARole', 'PASS02'
-- or --
2) exec sp_setapprole 'ARole', 'PASS02', 'ODBC'
What puzzles me is why the original code is working? And how the password was set at the first place ?