Our developers gain access to SQL Server and specific databases via
membership in Windows Groups. They recently reported that using Management
Studio to view the properties of a stored procedure brought up the following
error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

------------------------------

Property DefaultSchema is not available for Database '[xxxxx]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)

For , click:
http://go.microsoft.com/fwlink?ProdN...a&LinkId=20476

------------------------------
BUTTONS:

OK


According to the developer this problems just started happening. They
believe we changed something in SQL Server that caused this problem to
appear. We are trying to determine if something we did has caused this
problem, and what options we have at resolving this problem.

Here are some additional facts and recent changes that might have caused
this problem:

1) Developer DOMAIN\DEVELOPER gains access to SQL Server via Windows group
"WG"
2) User "U" has been created in database A, B and C that is associated with
login "WG"
3) A Role "R" has been created in each database (A, B and C) and user "U"
has been placed in this role
4) Role "R" has been given "VIEW DEFINITION" permissions in each database
(A, B, and C) and has also been placed in the “db_datareader� role.
5) Yesterday developer DOMAIN\DEVELOPER was able to right click on any
stored procedure in database A, B, and C and select "Properties" to view the
"Stored Procedure Properties ...." dialog box. (note: this is an assumed fact
since we can’t go back and prove that this is the way it worked now since
this is failing. But I have asked multiple developers and they say this use
to work prior to today. So I am assuming this to be a valid fact).
6) Today we migrated database D to our server
4) Created role "R" in database D
8) Granted "VIEW DEFINITION" permissions to role "R" in database D.
9) Place role “R� in db_datareader role
9) Created user "U" in database D and associated login "WG" with this user.
10) Place user "U" in role "R" in database D

--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com

This isn't really "" but at least confirmation that Microsoft is aware
of the problem and will fix it (hopefully in SP3):

http://connect.microsoft.com/SQL/fee...edbackID=12571
3





On 9/11/08 10:37 AM, in article
F9FD0820-CB41-4217-BE34-447F6416857B@microsoft.com, "Greg Larsen"
<gregalarsen@removeit.msn.com> wrote:

> Our developers gain access to SQL Server and specific databases via
> membership in Windows Groups. They recently reported that using Management
> Studio to view the properties of a stored procedure brought up the following
> error:
>
> TITLE: Microsoft SQL Server Management Studio
> ------------------------------
>
> Cannot show requested dialog.
>
> ------------------------------
> ADDITIONAL INFORMATION:
>
> Cannot show requested dialog. (SqlMgmt)
>
> ------------------------------
>
> Property DefaultSchema is not available for Database '[xxxxx]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
>
> For , click:
> http://go.microsoft.com/fwlink?ProdN...dVer=9.00.3042
> .00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exce ptionTemplates.PropertyCanno
> tBeRetrievedExceptionText&EvtID=DefaultSchema&Link Id=20476
>
> ------------------------------
> BUTTONS:
>
> OK
>
>
> According to the developer this problems just started happening. They
> believe we changed something in SQL Server that caused this problem to
> appear. We are trying to determine if something we did has caused this
> problem, and what options we have at resolving this problem.
>
> Here are some additional facts and recent changes that might have caused
> this problem:
>
> 1) Developer DOMAIN\DEVELOPER gains access to SQL Server via Windows group
> "WG"
> 2) User "U" has been created in database A, B and C that is associated with
> login "WG"
> 3) A Role "R" has been created in each database (A, B and C) and user "U"
> has been placed in this role
> 4) Role "R" has been given "VIEW DEFINITION" permissions in each database
> (A, B, and C) and has also been placed in the ³db_datareader² role.
> 5) Yesterday developer DOMAIN\DEVELOPER was able to right click on any
> stored procedure in database A, B, and C and select "Properties" to view the
> "Stored Procedure Properties ...." dialog box. (note: this is an assumed fact
> since we can¹t go back and prove that this is the way it worked now since
> this is failing. But I have asked multiple developers and they say this use
> to work prior to today. So I am assuming this to be a valid fact).
> 6) Today we migrated database D to our server
> 4) Created role "R" in database D
> 8) Granted "VIEW DEFINITION" permissions to role "R" in database D.
> 9) Place role ³R² in db_datareader role
> 9) Created user "U" in database D and associated login "WG" with this user.
> 10) Place user "U" in role "R" in database D

Thanks for the link. Not exactly the same issue, but very similar. The
issue discussed that the Windows Users wants to create an object. In my case
the Windows Users only wants to view an object. I would think viewing an
object should not require a default schema at all, but of course when you
create an object you need to have a schema to associate with the object being
created.


--
If you are looking for SQL Server examples or a free SQL Server DBA
Dashboard tool check out my Website at http://www.sqlserverexamples.com


"Aaron Bertrand [SQL Server MVP]" wrote:

> This isn't really "" but at least confirmation that Microsoft is aware
> of the problem and will fix it (hopefully in SP3):
>
> http://connect.microsoft.com/SQL/fee...edbackID=12571
> 3
>
>
>
>
>
> On 9/11/08 10:37 AM, in article
> F9FD0820-CB41-4217-BE34-447F6416857B@microsoft.com, "Greg Larsen"
> <gregalarsen@removeit.msn.com> wrote:
>
> > Our developers gain access to SQL Server and specific databases via
> > membership in Windows Groups. They recently reported that using Management
> > Studio to view the properties of a stored procedure brought up the following
> > error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > ------------------------------
> >
> > Cannot show requested dialog.
> >
> > ------------------------------
> > ADDITIONAL INFORMATION:
> >
> > Cannot show requested dialog. (SqlMgmt)
> >
> > ------------------------------
> >
> > Property DefaultSchema is not available for Database '[xxxxx]'. This
> > property may not exist for this object, or may not be retrievable due to
> > insufficient access rights. (Microsoft.SqlServer.Smo)
> >
> > For , click:
> > http://go.microsoft.com/fwlink?ProdN...dVer=9.00.3042
> > .00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exce ptionTemplates.PropertyCanno
> > tBeRetrievedExceptionText&EvtID=DefaultSchema&Link Id=20476
> >
> > ------------------------------
> > BUTTONS:
> >
> > OK
> >
> >
> > According to the developer this problems just started happening. They
> > believe we changed something in SQL Server that caused this problem to
> > appear. We are trying to determine if something we did has caused this
> > problem, and what options we have at resolving this problem.
> >
> > Here are some additional facts and recent changes that might have caused
> > this problem:
> >
> > 1) Developer DOMAIN\DEVELOPER gains access to SQL Server via Windows group
> > "WG"
> > 2) User "U" has been created in database A, B and C that is associated with
> > login "WG"
> > 3) A Role "R" has been created in each database (A, B and C) and user "U"
> > has been placed in this role
> > 4) Role "R" has been given "VIEW DEFINITION" permissions in each database
> > (A, B, and C) and has also been placed in the ³db_datareader² role.
> > 5) Yesterday developer DOMAIN\DEVELOPER was able to right click on any
> > stored procedure in database A, B, and C and select "Properties" to view the
> > "Stored Procedure Properties ...." dialog box. (note: this is an assumed fact
> > since we can¹t go back and prove that this is the way it worked now since
> > this is failing. But I have asked multiple developers and they say this use
> > to work prior to today. So I am assuming this to be a valid fact).
> > 6) Today we migrated database D to our server
> > 4) Created role "R" in database D
> > 8) Granted "VIEW DEFINITION" permissions to role "R" in database D.
> > 9) Place role ³R² in db_datareader role
> > 9) Created user "U" in database D and associated login "WG" with this user.
> > 10) Place user "U" in role "R" in database D
>
>

Is there a fix out for this yet? I've found plenty of articles where others
are having this exact same issue but nothing to indicate if it is resolved in
a hot fix.

"Greg Larsen" wrote:

> Thanks for the link. Not exactly the same issue, but very similar. The
> issue discussed that the Windows Users wants to create an object. In my case
> the Windows Users only wants to view an object. I would think viewing an
> object should not require a default schema at all, but of course when you
> create an object you need to have a schema to associate with the object being
> created.
>
>
> --
> If you are looking for SQL Server examples or a free SQL Server DBA
> Dashboard tool check out my Website at http://www.sqlserverexamples.com
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
> > This isn't really "" but at least confirmation that Microsoft is aware
> > of the problem and will fix it (hopefully in SP3):
> >
> > http://connect.microsoft.com/SQL/fee...edbackID=12571
> > 3
> >
> >
> >
> >
> >
> > On 9/11/08 10:37 AM, in article
> > F9FD0820-CB41-4217-BE34-447F6416857B@microsoft.com, "Greg Larsen"
> > <gregalarsen@removeit.msn.com> wrote:
> >
> > > Our developers gain access to SQL Server and specific databases via
> > > membership in Windows Groups. They recently reported that using Management
> > > Studio to view the properties of a stored procedure brought up the following
> > > error:
> > >
> > > TITLE: Microsoft SQL Server Management Studio
> > > ------------------------------
> > >
> > > Cannot show requested dialog.
> > >
> > > ------------------------------
> > > ADDITIONAL INFORMATION:
> > >
> > > Cannot show requested dialog. (SqlMgmt)
> > >
> > > ------------------------------
> > >
> > > Property DefaultSchema is not available for Database '[xxxxx]'. This
> > > property may not exist for this object, or may not be retrievable due to
> > > insufficient access rights. (Microsoft.SqlServer.Smo)
> > >
> > > For , click:
> > > http://go.microsoft.com/fwlink?ProdN...dVer=9.00.3042
> > > .00&EvtSrc=Microsoft.SqlServer.Management.Smo.Exce ptionTemplates.PropertyCanno
> > > tBeRetrievedExceptionText&EvtID=DefaultSchema&Link Id=20476
> > >
> > > ------------------------------
> > > BUTTONS:
> > >
> > > OK
> > >
> > >
> > > According to the developer this problems just started happening. They
> > > believe we changed something in SQL Server that caused this problem to
> > > appear. We are trying to determine if something we did has caused this
> > > problem, and what options we have at resolving this problem.
> > >
> > > Here are some additional facts and recent changes that might have caused
> > > this problem:
> > >
> > > 1) Developer DOMAIN\DEVELOPER gains access to SQL Server via Windows group
> > > "WG"
> > > 2) User "U" has been created in database A, B and C that is associated with
> > > login "WG"
> > > 3) A Role "R" has been created in each database (A, B and C) and user "U"
> > > has been placed in this role
> > > 4) Role "R" has been given "VIEW DEFINITION" permissions in each database
> > > (A, B, and C) and has also been placed in the ³db_datareader² role.
> > > 5) Yesterday developer DOMAIN\DEVELOPER was able to right click on any
> > > stored procedure in database A, B, and C and select "Properties" to view the
> > > "Stored Procedure Properties ...." dialog box. (note: this is an assumed fact
> > > since we can¹t go back and prove that this is the way it worked now since
> > > this is failing. But I have asked multiple developers and they say this use
> > > to work prior to today. So I am assuming this to be a valid fact).
> > > 6) Today we migrated database D to our server
> > > 4) Created role "R" in database D
> > > 8) Granted "VIEW DEFINITION" permissions to role "R" in database D.
> > > 9) Place role ³R² in db_datareader role
> > > 9) Created user "U" in database D and associated login "WG" with this user.
> > > 10) Place user "U" in role "R" in database D
> >
> >