CODEWITHSUNDEEP
sqlsql-serverdatabase
mengchew0113
mengchew0113

Reputation: 419

Can't Create Database Diagrams

In SQL 2008, Is it a way to grant permission to developer for view and create database diagrams without giving them dbo permission?

Upvotes: 8

Views: 23094

Answers (6)

Mohammad Swedan
Mohammad Swedan

Reputation: 11

EXEC sp_changedbowner 'sa'; --as simple as this :-)

Upvotes: 1

NR Ganesh
NR Ganesh

Reputation: 111

I had permissions, yet i couldn't create diagram. After doing this, it worked

SELECT @@SERVERNAME; -- get your server name

ALTER AUTHORIZATION ON DATABASE::[*databasename*] TO [*servername*\Administrator];

Upvotes: 2

Ashraf Sada
Ashraf Sada

Reputation: 4905

You need to change database owner to be able to create database diagrams.

To change the database owner:

ALTER AUTHORIZATION ON DATABASE::AshrafTestDB TO sa

Then refresh databases in object explorer in SSMS.

Now you should be able to add new diagrams.

Upvotes: 11

sead4711
sead4711

Reputation: 29

you can find a good description here: https://dba.stackexchange.com/questions/43946/permission-required-to-view-a-database-diagram

and here: https://www.mssqltips.com/sqlservertip/1816/getting-started-with-sql-server-database-diagrams/

These have helped me to solve my issue.

Upvotes: 0

abdelrhman raafat
abdelrhman raafat

Reputation: 161

To take credentials to create db. Diagram, Try this query:

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false

Upvotes: 16

Pondlife
Pondlife

Reputation: 16240

From Books Online:

To use Database Diagram Designer, it must first be set up by a member of the db_owner role to control access to diagrams.

And

any user with access to a database can create a diagram

In other words, a user with db_owner permissions must first enable diagrams in the database (to create the sysdiagrams table) and then anyone can create them. See Books Online for more information.

Upvotes: 2

Related Questions