Database diagram or Entity Relationship Diagram (ERD) is a very handy tool in the MS SQL server management studio. I found it very useful when working with a large relational database with many tables (entities). It helps to understand the data modelling by visualizing the table structures along with their relationships. To create a database diagram, you have to login to the SQL Server management studio. You can download it from the Microsoft website. This is a very handy open source tool for database management. As a developer, I use this regularly to manage the MS SQL databases. It has some advanced features for data export/import including database permissions. Once you login, you will see all the current databases on the local server or remote server.
To create a database diagram, expand the database by clicking on the + sign before the database name. You can then click on the Database Diagrams to create or view existing database diagrams. You can add database objects as you like or remove objects.
Since you logged in as windows builtin user (sa). you might get an error when trying to generate the diagram due to permission and authorization issue. You might get an error like this –
Now fixing this error is not hard. You can execute a command to grant the authorization to the default “sa” user. To do that, open a new Query window then type the following command.
ALTER AUTHORIZATION ON DATABASE::RentMyWrox TO [sa];
The above code is very straightforward. I am altering the Authorization scheme to grant access to the “RentMyWrox” database to the user “sa”.
This will now allow you to create a diagram. Here is the Entity Relationship Diagram (ERD) of the RentMyWrox database.
