Only a System Administrator or the Database Owner can change a user’s database options by executing sp_dboption. Users aliased to the Database Owner cannot change database options with sp_dboption.
You must be using the master database to execute sp_dboption. Then, for the change to take effect, you must issue the checkpoint command while using the database for which the option was changed.
Remember that you cannot change any master database options.
To change pubs2 to read only:
use master sp_dboption pubs2, "read only", true
Then, run the checkpoint command in the database that was changed:
checkpoint pubs2
For the optname parameter of sp_dboption, Adaptive Server understands any unique string that is part of the option name. To set the trunc log on chkpt option:
use master sp_dboption pubs2, trunc, true
If you enter an ambiguous value for optname, an error message is displayed. For example, two of the database options are dbo use only and read only. Using “only” for the optname parameter generates a message because it matches both names. The complete names that match the string supplied are printed out so that you can see how to make the optname more specific.
You can turn on more than one database option at a time. You cannot change database options inside a user-defined transaction.
