Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Change the Trustworthy Option in SQL Server 2005

Posted on 2007-10-02
8
Medium Priority
?
9,697 Views
Last Modified: 2012-08-13
I would like to change the Trustworthy option by executing:
  ALTER DATABASE DatabaseName SET TRUSTWORTHY ON
But I receive the error:
 Msg 5011, Level 14, State 5, Line 4
User does not have permission to alter database 'DatabaseName', or the database does not exist.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.
 
But of course, all permissions seem correct and the user is in the sysadmin group. Also, the Trustworthy option is disabled in all databases but it is the only option that is. I can create a new database with the option set to ON.

Any idea how to change the Trustworthy option on an existing database?
0
Comment
Question by:TomCoury
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 19998745
you must be a member of the sysadmin fixed server role, thats pretty much it.  are you logged in under windows authentication or sql server authentication?
0
 

Author Comment

by:TomCoury
ID: 19998774
Windows authentication. I also tried connecting as SA.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 19998792
You are using the correct command.

The database exists. I assume that these are user-defined databases, not system.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:TomCoury
ID: 19998839
All databases are user defined. I can connect to my db instance with my account or the SA account. Both have sysadmin roles. If I check the database options, every user database has the Trustworthy option disabled but all other options are enabled. This situation also exists if I launch management studio from the server.
0
 
LVL 27

Accepted Solution

by:
ptjcb earned 2000 total points
ID: 19998948
I changed two of my user defined databases from OFF to ON and back to OFF.

ALTER DATABASE databasename SET TRUSTWORTHY OFF;

Can you do another ALTER database command on that database? I would like to determine if this is a permissions or database issue.

Can you do this?

Only do this if the database is not a production database. Only do this on a test server, please. I do not want to destroy any backups.


USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO


0
 

Author Comment

by:TomCoury
ID: 19999142
OK. You're not going to believe this. I executed the following commands:
USE master;
GO
ALTER DATABASE TC
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TC
SET READ_ONLY;
GO
ALTER DATABASE TC
SET MULTI_USER;
GO
ALTER DATABASE TC
SET TRUSTWORTHY ON

And they all worked. Then I ran the ALTER DATABASE dbname SET TRUSTWORTHY ON on the other databases, and they worked also.

I don't get it. I never changed users. You must be a genius... Thanks. Any ideas what may have happened?
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 19999152
Lauging...according to Experts-Exchange I am.


The only difference is that I added a USE MASTER

0
 

Author Comment

by:TomCoury
ID: 19999191
I thought that might have caused the problem but I executed ALTER DATABASE dbname SET TRUSTWORTHY ON within the database and it still worked. I wish I knew what fixed it.

Thanks again.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question