dboption

I have quite a few issues with my sql 2000 server.
1. when execute sp_dboption 'northwind', 'read only', 'true'
I got the following messages:
Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
Database option 'read only' does not exist.
It seems that all sp_dboption command are not allowed in the server any more.
2. When I tried to drop a database, the checkbox for
"delete backup and restore history for the database" is grayed out. I was
not able to check this checkbox any more.
3. When I try to create a new database diagram, got the following error
message:
You do not have sufficient privilege to create a new database diagram
4. I tried to following the instructions suggested from knowledge base:
1. In SQL Enterprise Manager, move to the affected database.
2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
database role of the dtproperties table.
3. Grant EXEC permissions for the public database role to all these stored
procedures:
dt_addtosourcecontrol
dt_addtosourcecontrol_u
dt_adduserobject
dt_adduserobject_vcs
dt_checkinobject
dt_checkinobject_u
dt_checkoutobject
dt_checkoutobject_u
dt_displayoaerror
dt_displayoaerror_u
dt_droppropertiesbyid
dt_dropuserobjectbyid
dt_generateansiname
dt_getobjwithprop
dt_getobjwithprop_u
dt_getpropertiesbyid
dt_getpropertiesbyid_u
dt_getpropertiesbyid_vcs
dt_getpropertiesbyid_vcs_u
dt_isundersourcecontrol
dt_isundersourcecontrol_u
dt_removefromsourcecontrol
dt_setpropertybyid
dt_setpropertybyid_u
dt_validateloginparams
dt_validateloginparams_u
dt_vcsenabled
dt_verstamp006
dt_whocheckedout
dt_whocheckedout_u
I check all the checkbox for the above right from role-pulic(permission),
after I hit apply and go back to see if the changes have been applied to the
public role, I found that all the checkbox that I checked could not be saved
and they were unchecked.
I have more than 20 databases on this server, the above problem occured to
all the databases including master database.
Could anyone provide any help? Could it be possible that all the database
options to public role have been disabled by mistakes? What are the command
to enable all the dboptions for master database? Is master database
corrupted?
Thank you very much for your help.
Hi
Something like:
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only' , @.optvalue =
'true'
should work fine.
Execute permissions to change an option (using sp_dboption with all
parameters) default to members of the sysadmin and dbcreator fixed server
roles and the db_owner fixed database role, execute permissions to display
the options currently set in a database default to all users, therefore
check if you can
USE MASTER
EXEC sp_dboption @.dbname = 'pubs', @.optname = 'read only'
If not it would seem that someone has changed the options.
John
"she" <she@.discussions.microsoft.com> wrote in message
news:081233B1-6917-4C19-BB33-4D326DDC4DE8@.microsoft.com...
>I have quite a few issues with my sql 2000 server.
> 1. when execute sp_dboption 'northwind', 'read only', 'true'
> I got the following messages:
> Server: Msg 15011, Level 16, State 1, Procedure sp_dboption, Line 130
> Database option 'read only' does not exist.
> It seems that all sp_dboption command are not allowed in the server any
> more.
> 2. When I tried to drop a database, the checkbox for
> "delete backup and restore history for the database" is grayed out. I was
> not able to check this checkbox any more.
> 3. When I try to create a new database diagram, got the following error
> message:
> You do not have sufficient privilege to create a new database diagram
> 4. I tried to following the instructions suggested from knowledge base:
> 1. In SQL Enterprise Manager, move to the affected database.
> 2. Grant SELECT, INSERT, UPDATE, DELETE and DRI permissions for the public
> database role of the dtproperties table.
> 3. Grant EXEC permissions for the public database role to all these stored
> procedures:
> dt_addtosourcecontrol
> dt_addtosourcecontrol_u
> dt_adduserobject
> dt_adduserobject_vcs
> dt_checkinobject
> dt_checkinobject_u
> dt_checkoutobject
> dt_checkoutobject_u
> dt_displayoaerror
> dt_displayoaerror_u
> dt_droppropertiesbyid
> dt_dropuserobjectbyid
> dt_generateansiname
> dt_getobjwithprop
> dt_getobjwithprop_u
> dt_getpropertiesbyid
> dt_getpropertiesbyid_u
> dt_getpropertiesbyid_vcs
> dt_getpropertiesbyid_vcs_u
> dt_isundersourcecontrol
> dt_isundersourcecontrol_u
> dt_removefromsourcecontrol
> dt_setpropertybyid
> dt_setpropertybyid_u
> dt_validateloginparams
> dt_validateloginparams_u
> dt_vcsenabled
> dt_verstamp006
> dt_whocheckedout
> dt_whocheckedout_u
> I check all the checkbox for the above right from role-pulic(permission),
> after I hit apply and go back to see if the changes have been applied to
> the
> public role, I found that all the checkbox that I checked could not be
> saved
> and they were unchecked.
> I have more than 20 databases on this server, the above problem occured to
> all the databases including master database.
> Could anyone provide any help? Could it be possible that all the database
> options to public role have been disabled by mistakes? What are the
> command
> to enable all the dboptions for master database? Is master database
> corrupted?
> Thank you very much for your help.
>