Data model access after a SQL Server database restore


By:   |   Updated: 2006-11-13   |   Comments (1)   |   Related: More > Database Design

Problem
After a database restore, why is it possible for me to access all of my objects except for the data model I created?  Did I back it up correctly?  Is it a separate backup and recovery process?  Has it been corrupted? 

This is the error message I am receiving:

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Solution
With the recent tips (
SQL Server 2005 Exposed = Data Modeling Tools and SQL Server Data Modeling Tools) on data modeling, we outlined the dbo.sysdiagrams table supporting the data models.  So the normal backup and recovery process will include this table.  So no special steps need to be taken for the backup and recovery process.

In addition, no special steps are needed to create the database diagram support objects because they already exist and Management Studio appears to create these when needed.

A special step that needs to be considered is changing the database ownership, back to the owner that originally created the database when the digrams were created.  This can be accomplished by issuing these statements in a Query Editor window in SQL Server 2005 Management Studio:

-- change to the desired user database first

EXEC sp_helpdb;
GO
EXEC sp_changedbowner 'sa';
GO
EXEC sp_helpdb;
GO

In my circumstance, "sa" is the needed owner and changing the database owner to "sa" resolved this issue under the circumstances.  Nevertheless, having "sa" own the user defined databases is a best practice.  This scenario could be one of a handful of viable reasons to ensure that the sa login is the owner of the database. 

Next Steps

  • When you restore databases from one environment to another, be sure to check the database ownership because some of the tools and commands default to the login performing the process, which may not necessarily be the desired login.
  • As you restore your databases, ensure the database ownership is accurate to prevent the data model ownership issues.  Consider this verification and correction process during every database restore.
  • If you are not currently using the SQL Server data modeling tools in Management Studio, check out these tips:


Last Updated: 2006-11-13


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, November 13, 2013 - 4:05:41 PM - Deepson Back To Top

Hello,

  Can you please help in solving the answers?

http://ithooters.com/blog/database/sql-server-interview-questions-intermediate-level/



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools