SSMS ER Diagram Tutorial

Can you make an ER diagram in SSMS?

Yes. SSMS includes the Database Designer, which Microsoft describes as a visual tool to design and visualise a database you are connected to. A database diagram shows some or all of the tables, columns, keys and relationships, and you can create as many diagrams per database as you like, for example one large diagram with every column and a smaller one showing table names only. Each diagram is stored inside the database it describes.

Two points set expectations. First, it is a physical diagram: you work against a live database and saving writes the changes to it. Second, the feature is current in SSMS 22: Microsoft's FAQ lists "database diagrams and table designers" among the reasons to use SSMS, and the 22.2.1 release notes record two Database Diagrams fixes. The documentation applies to SQL Server, Azure SQL Database and Azure SQL Managed Instance, although a few pages (saving diagrams, drawing relationships) list SQL Server only.

One-time set-up and permissions

Before anyone can draw diagrams in a database, a member of the db_owner role has to set up diagramming:

1. In Object Explorer, expand the database.
2. Expand the Database Diagrams node.
3. When asked whether to set up database diagramming, select Yes. (When you start from New Database Diagram instead, the message reads "This database doesn't have one or more of the support objects required to use database diagramming. Do you wish to create them?")

This creates the sysdiagrams table, the procedures sp_creatediagram, sp_alterdiagram, sp_dropdiagram, sp_renamediagram, sp_helpdiagrams, sp_helpdiagramsdefinition and sp_upgraddiagrams, and the function fn_diagramobjects. On a shared or production database, agree this with the owner first, because it adds objects to the schema.

Ownership. Any user with access can create a diagram, but each diagram has a single owner, and only that owner and db_owner members can see or open it. To change tables through a diagram you also need permission to alter them; otherwise SSMS shows an error.

Create a database diagram, step by step

1. In Object Explorer, right-click the Database Diagrams folder and choose New Database Diagram.
2. The Add Table dialog appears. Select the tables you want and choose Add, then close the dialog. You can add more later.
3. To pull in neighbours, right-click a table and choose Add Related Tables; SSMS adds every table that has a relationship with it.
4. To change how much each table shows, right-click it, point to Table View and pick Standard, Column Names, Keys, Name Only or Custom (set up with Modify Custom).
5. Rearrange the layout by dragging tables; the layout options also include autosizing, arranging tables, text annotations and font changes.
6. Save from the File menu. A new diagram asks for a name; for changed tables the Save dialog lists what will be written to the database. Choose Yes (or OK) to apply.

To reopen an existing diagram later, right-click it under Database Diagrams and choose Design Database Diagram. The default table view and whether Add Table opens automatically are set in Tools > Options > Designers > Table and Database Designers.

Reading and drawing relationships

Relationship lines follow Microsoft's notation:

• Endpoints: a key at one end and a figure-eight symbol at the other is one-to-many; a key at both ends is one-to-one. The foreign-key table sits at the figure-eight end.
• Line style: a solid line means referential integrity is enforced when rows are added or changed; a dotted line means it is not enforced.
• Same table at both ends: a reflexive (self-referencing) relationship.
• Primary keys show a key symbol in the row selector.

To draw a foreign key: select the row selector of the column (or columns) in one table and drag it onto the related table. The Tables and Columns dialog opens in front of Foreign Key Relationship; check the relationship name (default FK_localtable_foreigntable), the primary key table and the column mapping, choose OK, adjust properties, and choose OK again. The columns on the primary key side must be part of a primary key or unique constraint.

Saving can fail for changes that require SQL Server to rebuild a table (for example inserting a column in the middle or changing a data type) when the Prevent saving changes that require table re-creation option is on. Review those changes carefully, or script them, rather than switching the safeguard off on a production database.

Getting the diagram out of SSMS

Microsoft documents one way to export the picture: open the diagram, right-click a blank area and choose Copy Diagram to Clipboard. The whole diagram is copied as an image that you can paste into a document or image editor. Only the diagram owner or a db_owner member can open it to do this. The SSMS documentation does not describe saving a diagram directly as a PNG, SVG or PDF file, and the diagram itself only exists inside the database.

Limits, and when to use another tool

• Live database only. There is no offline model, no logical/physical model split and no forward or reverse engineering to a file.
• Permissions. The set-up needs db_owner, and diagrams are private to their owner and db_owner members.
• Windows only, like the rest of SSMS.
• Image export is clipboard copy only.

The MSSQL extension for VS Code has a Schema Designer (right-click a database, Visualize and Design Schema) with auto-arrange, search and filters, generated T-SQL scripts and diagram export, and it runs on macOS and Linux; see the VS Code MSSQL extension review and SSMS vs VS Code. For dedicated modelling tools, see the best ER diagram tools.

Frequently asked questions

Sources

• Microsoft Learn: Design database diagrams (Visual Database Tools)
• Microsoft Learn: Set up Database Diagram Designer
• Microsoft Learn: Understand database diagram ownership
• Microsoft Learn: Create a new database diagram
• Microsoft Learn: Walkthrough, add and change a database diagram
• Microsoft Learn: Open Database Diagram Designer
• Microsoft Learn: Work with diagram layout
• Microsoft Learn: Add related tables to diagrams
• Microsoft Learn: Customize the amount of information displayed in diagrams
• Microsoft Learn: Create relationships between tables on a diagram
• Microsoft Learn: Save database diagrams
• Microsoft Learn: Copy an image of a database diagram to the clipboard
• Microsoft Learn: Options (Designers, Table and Database Designers page)
• Microsoft Learn: SSMS 22 release notes
• Microsoft Learn: SSMS FAQ
• Microsoft Learn: Schema Designer (MSSQL extension for VS Code)

Checked 8 October 2026.

How we research tool guides: our editorial method. CodeWithSQL earns nothing from the vendors mentioned.