sql server 2005 diagram permissions

DB_GIRL
DB_GIRL used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

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

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

Open in new window

Most Valuable Expert 2014
Commented:
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)
http://msdn.microsoft.com/en-us/library/ms186345%28SQL.90%29.aspx

Default Schema for Windows Group
https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=238246&wa=wsignin1.0&siteid=68

View diagram without db_owner permission
http://www.sqlservercentral.com/Forums/Topic649593-359-1.aspx

How to let user to view diagrams
http://www.sqlservercentral.com/Forums/Topic727904-146-1.aspx

Author

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

Commented:
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