sql server 2005 diagram permissions

DB_GIRL used Ask the Experts™
One of our QAs is  a part of an NT Group, that  in turn has  a login on a SQL Server 2005, and a member of db_datareader role on a database.
This QA was able to create a diagram on that database in SSMS, but once it was created she hasn't been able to see it.
I read that only the diagram creator or a member of db_owner role can acceess the diagram.
She created the diagram, but she can't see it. Any permission I can give to her (not db_owner) so that she can see her diagram?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mohed SharfiVice CEO

Please try this to able create diagram.
REVOKE EXECUTE ON OBJECT::YourDatabaseName.sp_creatediagram
FROM YourUserName;

Open in new window

Most Valuable Expert 2014
You are running into a confluence of 2 issues here.

When you connect to SQL using Windows group, you cannot define a default schema. It is greyed out. This means that if you want everyone to be in a specific schema, you can't do it by default. When users connect through membership in a Windows group, SQL will automatically create a separate user (mapped to the AD acct), and a schema of the same name.

So the issue here is that when she created the diagram -- it defaulted out to the dbo instead of to her schema. Then when she goes back -- having only data reader, she isn't considered the owner.

The work around might be to grant her access as the MyDomain\HerUserId instead of using the group. Then it might work.

SQL Server 2005 Books Online (November 2008)
Understanding Database Diagram Ownership (Visual Database Tools)

Default Schema for Windows Group

View diagram without db_owner permission

How to let user to view diagrams


This was the correct explanation of the problem. I was able to understand the reason for the issue.
Most Valuable Expert 2014

Glad to be of assistance. May all your days get brighter and brighter.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial