Want To Be Able To Create Or View Database Diagrams
Jan 21, 2006
Our MS SQL (SQL Server 2000) DBA has database privileges locked down
pretty tightly. We end users/developers do not have administrator
privileges for most databases. That arrangement has worked out OK for
the most part. However, it's a bit aggravating that we can't even
create our own database diagrams. When we attempt to do so (in
Enterprise Manager), we get a dialog that says "You do not have
sufficient privilege to create a new database diagram."
Our DBA is so busy that it's difficult to get them to create ones for
us. And even when they do, it seems that we can't even view it online
-- we rely on the DBA to give us printed copies or screenshots for our
documentation. How ridiculous is that? We'd also like to be able to
manipulate the diagrams online so that we can more easily study our
table structures, indexes, foreign key constraints, etc. In fact,
there doesn't even seem to be any other way to easily view current
foreign key constraints.
I realize that this might have to do with the diagramming tool having
the ability to actually manipulate the database in ways we aren't
supposed to do (e.g., like creating new tables). However, isn't there
a "read-only" version of this tool that will give us what I'm looking
for? Short of that, are there any other free or cheap tools that will
provide this? Thanks!
(Please forgive my ignorance if this has been hashed out before.
However, I did search news groups and the web before posting, figuring
this had been covered before, but I didn't find anything that
specifically addressed this issue.)
I can create a database, create tables and I can even delete the database but I cannot create database digrams. I get an error that says I don't have a valid dbo user or permissions to impersonate. It asks me if I would like to impersonate but it just fails. Any Ideas? Thanks
I am trying to save a newly created diagram in SQL Server 2005 SP1.
The error message is: ----Beginning of pasted error message---
===================================
Cannot insert the value NULL into column 'diagram_id', table 'adt_db.dbo.sysdiagrams'; column does not allow nulls. INSERT fails. The statement has been terminated. The 'sp_creatediagram' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider)
------------------------------ Program Location:
at Microsoft.VisualStudio.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite) at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)
----End of pasted error message---
The database I use has been upgraded from SQL Server 2000.
I am running in SQL Server 2000 Compatibility Mode (80) but changing the compatibility mode to SQL Server 2050 (90) does not make any difference with regards to this error.
I am getting the following error when trying to create a view in a database.
If I have db_owner rights so I should not have an issue? Any thoughts
TITLE: Microsoft SQL Server Management Studio ------------------------------
Property DefaultSchema is not available for Database '[GiftTraq]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors)
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind GO
/*STEP 2, #1*/
/* does it exist, if so drop it */ if exist (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[OrdersToSpain] GO
/* Create the View */ create view "OrdersToSpain" AS SELECT Orders.OrderID AS Order_ID, Orders.CustomerID AS Customer_ID, Orders.OrderDate AS Ordered_Date. Orders.ShippedDate AS Shipped_Date, Orders.ShipCountry AS DestinationSpain FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.ShipCounty LIKE '%SPAIN%' GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 6 Line 6: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7 Line 7: Incorrect syntax near '.'.
Is there a way to use an Oracle linked server to create a database diagram? Or do I have to bring the whole Oracle db into SQL7 and then create the diagram?
In our organization, we are using SQL Server 2005 Management studio as the client interface to access all our enterprise Databases.Most of the databases are built on SQL 2005 DB Server. However, We have one of the Database built on SQL Server 2000, which also has Database diagrams in it. Though We are able to view all the DB objects through SQL 2005 managment studio interface, but unable to view the diagrams. I understand that, the binary data of the diagrams are stored in "dtproperties" table on SQL 2000 but in SQL 2005, it is stored in "sysobjects"(not sure) table and both are entirely different.
Following are my questions. 1.Since the DB is still on SQL 2000,How can we view the diagram objects thru the SQL 2005 Enterprise manager.
2.For the migration process of SQL 2000 to SQL 2005, How can i migrate the database diagrams.
Hi All I am using SQL server 2000 and I would like to create Database diagrams but It doesn't give me any option to crate one. It only shows "Working with SQL Server 2000 diagrams" and "Refresh" with no "create new diagram" and in other servers it but its only in my desktop that doesn't and I have full access
I upsized an access backend to sql 2005. In the process, the relationships were lost. When I go to the database in Sql, right click on database diagrams, all I get is refresh and a link to go to learn about database diagrams. How can I recreate the diagrams?
I am trying to follow this walkthrough<br><br>http://msdn2.microsoft.com/en-us/library/ms233763(VS.80).aspx<br>And getting nowhere as it says I need to set up database diagrams which I cannot due to not being in db_owner role, or not being valid db user etc etc.I cannot find out how to set this up for the database I created so far in t his tutorial.I have searched and searched and got nowhere.I then went into SSMSE by chance and found a load of roles but none named db_owner. I then tried to add my database file but SSMSE wont let me drill down into the required folderI weep!!!!Please can anyone help?
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
I hope i'm in the right forum. Is there a way to create a DB diagram schema via ASP/HTML and publish it on-line - keeping it up-to-date? I want to at least output a diagram schema to HTML format? Any ideas. TIA!
Hi all, I am trying to make DATABASE DIAGRAMs....I have restored a database called Tel from a backup file...n in that i have all the tables and procedures..now whenever i m clicking on its DATABASE DIAGRAMS it gives me the error as:
TITLE: Microsoft SQL Server Management Studio ------------------------------
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.
------------------------------ so,what should i do??any suggestion will be helpful for me...thnks in advance... regards..
I remember I asked the similar question before.But I have not solve this.
I am working with SQL Server 2005. I just make a copy of the production database.
I use "tasks -> Generate Script" to copy the table structure and use "tasks -> import Data" to copy Data. But I can not copy the database diagrams,I need to keep the primary keys - foreign keys relations.
Please let me know how to copy database diagrams.I try many ways but I can not figure it out.
When I try to Create a Database Diagram it sends me an Error message like this:
Server Name: TICSQLEXPRESS Error Number: 207 Severity: 16 State: 1 Procedure: sp_upgraddiagrams Line Number: 55
Invalid column name 'uvalue'. Invalid column name 'uvalue'. Could not find stored procedure 'dbo.sp_upgraddiagrams'. Object is invalid. Extended properties are not permitted on 'dbo.sysdiagrams', or the object does not exist. Object is invalid. Extended properties are not permitted on 'dbo.sp_upgraddiagrams', or the object does not exist. (.Net SqlClient Data Provider)
How can I find that Stored Procedure 'dbo.sp_upgraddiagrams'? Why doesn't it work OK?
Hidoes anyone know of a way of giving developers read only access to adatabase diagram in Enterprise Manager (SQL Server 2000). The database wassupplied by a third party and we don't want them tinkering with it, butthey do need to be able to create additional tables etc. to extend thefunctionality of the package.TIAChloe Crowder
I€™ve just installed SQL 2008 Express CTP. Everything seems to be fine except that the folder €śDatabase Diagrams€? is absent in the tree of SQL Server Management Studio. It is the same for newly created databases and databases upgraded from 2005 Express. Any ideas how to get the diagrams back?
When I use the sql 2005 "Database Diagrams" tool for the first time I always get the message: "The Database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?"
1. Does anyone know what the " support objects" referred to are?
2. Why SQL doesn't automatically create these support objects for me? (e.g. do they require a lot of hard disk space so they shouldn't routinely be created?)
3. Is there any way to make Sql automatically create them?
If you can address any of these points I'd appreciate it.
When I try to access the database diagrams in AdventureWorks, I get the following message:
TITLE: Microsoft SQL Server Management Studio Express ------------------------------
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.
tried to add a third and fourth table to an exsiting relationship diagram in VS05 server explorer, when i click save i get an error "the operation could not be completed" i am able to create new diagrams but it seems every time i click save, and close the diagram the reopen it and add new tables then click save i get the same error message, i dont even try and create the actual relationship but just add a third table and boom the problem occurs thanks in advance
This question has been asked several times on this forum, but none of the answers I've seen have worked for me. I just upgraded from SQL Server 2000.
The error I'm getting is:
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.
This is a new database I created in SQL Server management studio. It's set to 90 compatibility level. I've tried the "alter authorization" command, which works, but it has no effect (the error is still returned).
The odd part is that if I go to Server Properties->Permissions, select the Login that owns this database, and click "Effective Permissions", I get the error:
Could not obtain information about Windows NT group/user '<username>', error code 0x6e. (Microsoft SQL Server, Error: 15404)
But I know for certain that this account is valid and that my machine can reach the domain controller: 1) I received no error when I added this Login to the server and 2) I'm logged into SQL Server Management Studio using the same login.
If I switch to the "sa" user, it works. But I'd really rather find out what I can't use Windows authentication.
I'm new to VWD 2008 Express and MSSQL 2005 Express development (not to programming in genral), and I have this problem with creating a self join using Database Diagram in VWD2008 Express.
In SQL 2005 Express (through VWD) it's not possible to add the table to the diagram twice and its not possible to give the table an alias either.
Is it a limitation in the SQL Express or am I doing something wrong ?
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Hi.I'm very new to this so I apologise in advance for asking the blindibly obvious. I have installed SQL Express and SQL Server Management Studio Express and I have downloaded and attached the Northwind sample database. I can see and edit the data in the tables but when I try to open the Database Diagram node I get the following message: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.I have no idea what a valid logon would be. Can somebody help? Thanks
I have database diagrams in my original db which was built with Management Studio. I now have scripts to build the database from scratch, but my diagrams are left behind, back in the original db. Is there any way to copy the original diagrams into the new db? I know in SS2000 the diagrams were in the dtproperties table; any idea where they are in SS2005?
When you create database diagrams in Enterprise Manager, the detailsfor constructing those diagrams is saved into the dtproperties table.This table includes an image field which contains most of the relevantinfomation, in a binary format.SQL Enterprise manager offers no way to script out those diagrams, soI have created two Transact SQL components, one User Function and oneUser Procedure, which together provide a means to script out thecontents of the dtproperties table, including all of the binary basedimage data, into a self documenting, easy to read script. This scriptcan be stowed away safely, perhaps within your versioning software,and it can subsequently be recalled and executed to reconstruct allthe original diagrams.The script is intelligent enough not to overwrite existing diagrams,although it does allow the user to purge any existing diagrams, ifthey so choose.Once these two objects have been added to any database, you may thenbackup (script out) the current database diagrams by executing thestored procedure, like this:Exec usp_ScriptDatabaseDiagramsBy default, all database diagrams will be scripted, however, if youwant to script the diagrams individually, you can execute the sameprocedure, passing in the name of a specific diagram. For example:Exec usp_ScriptDatabaseDiagrams 'Users Alerts'The Transact SQL code for the two objects is too long to paste here,but if you are interested, I will email it to you. Just drop me a noteat: Join Bytes! (Remove bothinstances of TAKE_THIS_OUT from my email address first!!)-Clay
I just installed Management Studio 2016 CP1. Unfortunately, the Database Diagrams branch does not show-up when I expand the individual databases in Object Explorer. Interestingly, they show-up in Management Studio 2008
I am having the same problem as above. Are there step-by-step instructions available to correct this problem. I have tried some of the solutions listed above and I still can't access the database diagram.
