SQL Server database does not have three solutions for valid owners.

Source: Internet
Author: User

SQL Server database does not have three solutions for valid owners.

Problem:

During the development process, the operating system had a problem and decided to reinstall the system. However, the SQL Server database file is not separated and the system file is directly split. Attaching a database file to the new system database is normal, but a problem occurs when you open the database relationship diagram, as shown in:


Solution: (3)

1. Right-click your database name → properties → files → owner → sa. (This method is found online and may fail)


2. Set the compatibility level to 90 (2005 to 90, to 80)

(1) code:
USE [master]
GO
EXEC dbo. sp_dbcmptlevel @ dbname = 'database name', @ new_cmptlevel = 90
GO

(2) mouse pinching

Alternatively, right-click the database you restored, choose Properties> Options> Compatibility Level, select sqlserver2005 (90), and click OK. In this case, a prompt is displayed when you expand the "database relationship diagram" node under the database. "This database lacks one or more support objects required to use the database relationship diagram. Are you sure you want to create them, select "yes.


3. Select your database, and then select Security → user → select dbo to open the properties page. If the login name is empty, create a new query and
Use [your database name]
EXEC sp_changedbowner 'sa'




Sqlserver2005, when you open the database relationship diagram, the prompt is: this database does not have a valid owner, so you cannot install the database relationship diagram support object

I also encountered this problem before. The solution is simple. Right-click the database attribute, select a file from the left-side menu, and change the owner to sa.

SQL database questioning causes and solutions

Because you have deleted the physical files of the database, but there are still some files in the database.

A. We use the default method to create A database for recovery (such as test ). You can go to SQL Server Enterprise Manager
.
B. Stop the database server.
C. Delete the log file test_log.ldf of the database you just generated and overwrite the generated data with the mdf file of the database you want to restore.
Database Data File test_data.mdf.
D. Start the database server. The database test status is "Suspect ". At this time, you cannot perform any * operation on this database.
E. Set the database to allow direct * as a system table. You can select the database Server in SQL Server Enterprise Manager.
Server, right-click and select "properties". On the "Server Settings" Page, select "allow direct modification to system directory. Yes.
Use the following statement.
Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
F. Set test to emergency repair mode.
Update sysdatabases set status =-32768 where dbid = DB_ID ('test ')
In this case, you can see in SQL Server Enterprise Manager that the database is in "Read-Only/suspicious/offline/emergency mode ".
See the table in the database, but only the system table
G. perform real recovery * below to recreate the Database Log File
Dbcc rebuild_log ('test', 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/test_log.ldf ')
If the following prompt is displayed during execution:

Server: Message 5030, level 16, status 1, Row 1
Failed to lock the database to execute this.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

This indicates that other programs are using this database. If you used SQL Server Enterprise Manager in step F just now
Open the system table of the test database, and exit SQL Server Enterprise Manager.

The prompt for correct execution should be similar:

Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. Dbcc checkdb should be run to verify physical consistency
. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

In SQL Server Enterprise Manager, the database status is "for DBO only ". You can
The User table in the database is accessed.
H. Verify Database Consistency (omitted)
Dbcc checkdb ('test ')

The general execution result is as follows:

CHECKDB finds 0 allocation errors and 0 consistency errors (in the database 'test ).
After DBCC is executed, the full text is displayed.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.