Property QuotedIdentifierStatus is not available for UDF.. Help?

Hi ;
I'm using the release version of SQL Server 2005 tools (Microsoft SQL
Server Management Studio 9.00.1399.00) and .NET Framework 2.0
(2.0.50727.42) on Windows XP SP2.
My desire was to use the new tools against my existing SQL 2000 servers
as well as newly installed SQL 2005 so as to only have one place to
work in and access to new tool features. This has worked great except
for one issue. When trying to modify or script some table-valued
functions under Management Studio I get the following error:
===================================
Property QuotedIdentifierStatus is not available for
UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)
===================================
I can edit/script the function in the 2000 tools (Enterprise Manager
and SQL Analyzer) without problem. I can edit some table-valued
functions in Management Studio but not others. I have tried dropping
the functions and re-creating them in both 2000 and 2005 tools - the
create works fine in both places, but it remains
un-modifiable/scriptable in the 2005 tools.
Anyone else run into this problem?
Additional info available for the error
===================================
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32
index)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(String
propName)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](String
propName, T defaultValue)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectness(
String
ddlText, Boolean enforceCreate, Boolean checkName, String[]
expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulateTe
xt(String
ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
forCreate)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Boolea
n
forAlter)
at
Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.S
criptExistingTextObjectToWindow(IManaged
Connection
mc)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvokeTextObject(IManagedConnection
connection)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvoke()
===================================
Essa, M. Mughal
Software Developer
Toronto, CanadaCan you post a CREATE script for one of your problematic table-valued
functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"Essa" <essamughal@.hotmail.com> wrote in message
news:532E0285-F705-4CD7-BAE2-79C18B372B24@.microsoft.com...
> Hi ;
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
> ===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
> ===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
> Additional info available for the error
> ===================================
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int3
2
> index)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(Strin
g
> propName)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](Strin
g
> propName, T defaultValue)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectnes
s(String
> ddlText, Boolean enforceCreate, Boolean checkName, String[]
> expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulate
Text(String
> ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
> forCreate)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Bool
ean
> forAlter)
> at
> Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolea
n
> forAlter)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
. ScriptExistingTextObjectToWindow(IManage
dConnection
> mc)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.InvokeTextObject(IManagedConnection
> connection)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.Invoke()
> ===================================
> --
> Essa, M. Mughal
> Software Developer
> Toronto, Canada|||Essa (essamughal@.hotmail.com) writes:
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
>===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
>===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
Yes, this is a known bug, and it has been resolved as fixed. It only
affects connections to SQL 2000.
See also
http://lab.msdn.microsoft.com/produ...95-62d1fb31d0a4
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Yes, this is a known bug, and it has been resolved as fixed. It only
> affects connections to SQL 2000.
I should clarify that this does not mean that the fix is publicly
available. I only wanted to convey that Microsoft are aware of the
issue, and the fix is to be expected to appear in SP1 for SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool beans. Glad there's a fix.
I've got a bunch of SQL 2000 servers I need to script the schema for,
and I ran into the same UDF scripting problem myself.
Hope SP1 will be available soon.
- john
Erland Sommarskog wrote:
> Erland Sommarskog (esquel@.sommarskog.se) writes:
> I should clarify that this does not mean that the fix is publicly
> available. I only wanted to convey that Microsoft are aware of the
> issue, and the fix is to be expected to appear in SP1 for SQL 2005.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Property QuotedIdentifierStatus is not available

I'm using the release version of SQL Server 2005 tools (Microsoft SQL
Server Management Studio 9.00.1399.00) and .NET Framework 2.0
(2.0.50727.42) on Windows XP SP2.
My desire was to use the new tools against my existing SQL 2000 servers
as well as newly installed SQL 2005 so as to only have one place to
work in and access to new tool features. This has worked great except
for one issue. When trying to modify or script some table-valued
functions under Management Studio I get the following error:
===================================
Property QuotedIdentifierStatus is not available for
UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)
===================================
I can edit/script the function in the 2000 tools (Enterprise Manager
and SQL Analyzer) without problem. I can edit some table-valued
functions in Management Studio but not others. I have tried dropping
the functions and re-creating them in both 2000 and 2005 tools - the
create works fine in both places, but it remains
un-modifiable/scriptable in the 2005 tools.
Anyone else run into this problem?

>R<
Additional info available for the error
===================================
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
index)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional(String
propName)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional[T](String
propName, T defaultValue)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckTextCorectness(String
ddlText, Boolean enforceCreate, Boolean checkName, String[]
expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckAndManipulateText(String
ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
forCreate)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.GetTextHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.Smo.UserDefinedFunc tion.ScriptHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.ScriptExistingTextObjectT oWindow(IManagedConnection
mc)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.InvokeTextObject(IManaged Connection
connection)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.Invoke()
===================================
I have the same problem.
I have one UDF that I can edit in Management Studio. The only thing that
differs from the ones that I cant edit is that the editable function is an
Inline "Table-valued function". The other ones are "Multistatement
Table-valued" functions.
"Rajah" <rajahd@.zrgwortz.com> wrote in message
news:1131631336.774261.157340@.g49g2000cwa.googlegr oups.com...
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
> ===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
> ===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
>
> Additional info available for the error
> ===================================
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
> index)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional(String
> propName)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.Ge tPropValueOptional[T](String
> propName, T defaultValue)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckTextCorectness(String
> ddlText, Boolean enforceCreate, Boolean checkName, String[]
> expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.CheckAndManipulateText(String
> ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
> forCreate)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjec tBase.GetTextHeader(Boolean
> forAlter)
> at
> Microsoft.SqlServer.Management.Smo.UserDefinedFunc tion.ScriptHeader(Boolean
> forAlter)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.ScriptExistingTextObjectT oWindow(IManagedConnection
> mc)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.InvokeTextObject(IManaged Connection
> connection)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Ed itors.VsDocumentMenuItem.Invoke()
> ===================================
>

Property Promotion with multi-level XML data type

How would you extract data from an XML datatype column when it has multiple levels? I've done this with single levels using CROSS APPLY and the nodes method, but can't seem to grab data from the intermediate levels.

Example:

<Level1>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level3>abc</Level3>

<Level2>xyz</Level2>

<Level2>xyz</Level2>

</Level1>

For the root level, I use the xml.value() function, and for the details I use the cross apply xml.nodes() function typically, but if I use the xpath in the xml.nodes, I have to use the path to the lowest level (level3) to grab those values, but can't seem to grab the changing values at the intermediate levels (level2).

Would this involve multiple cross-apply instances?

I don't understand your example, why are those Level3 elements indented further to the right than the Level2 elements? They are both children of the root element Level1.

And it is not obvious what you want to extract.

|||

Sorry if this wasn't clear. The XML is supposed to represent a multiple-level heirarchy and the indentation indicates a parent-child relationship.

So level 1 can be Order, for example. At this level there can be several elements that define the Order (ID, Cust #, etc). Level 2 would be Order Type, like Internal, External, Global, Domestic (each order always has these 4 types associated with it). Level 3 would be details relevant to that order detail item, like part #, quantity, etc. (each Order has 4 Order Types, of which have multiple line-items within each order type).

So I need to return a table of data based on this single order. The results returned would have these fields:

Order ID (repeated for every order detail)

Cust # (repeated for every order detail)

OrderType (repeated for every order type/order detail)

Part # (unique per order detail/type)

Qty (unique per order detail/type)

Analogous to joining three relational tables (Order details -> Order Type -> Order) and getting the combined results of all three.

In SQL xquery, I use the .value function to get the Order ID and Cust# because they are at the root level of the XML field. I use the CROSS APPLY .nodes method to get to the lowest level of detail (Part# and Qty) and this produces the correct data. But for the intermediate level (Order Type) I can't seem to get to it.

If it still isn't clear, I'll send some actual XML.

Thanks

Kory

|||

Consider posting the XML and the query you have, then we can work from there to improve it.

|||

A coworker of mine helped me to figure this out:

I was doing this:

Before

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

And we changed it to this:

After

With Namespaces(....)
select
t.EventDateTime
,t.OrderId

,ref.value('(http://gns:OrderType/text())[1]','varchar(max)') OrderType

,ref.value('(//gns:OrderType/text())[1]','varchar(max)') OrderType
,ref.value('(ns:Product/text())[1]','varchar(max)') Product
,ref.value('(ns:Qty/text())[1]','varchar(max)') Qty
FROM
Orders t
CROSS APPLY
xmlfld.nodes ('//ns:OrderDetailItem') AS R(ref)

So the only change was to the xpath from //gnsrderType to http://gnsrderType

I really don't understand why this worked, but the first way just promoted the first occurance of the value, but the second method promoted the value when it changed.

-Kory

property probelm

We have a development machine, and a web server, both running SQL Reporting
Services. My problem is that when I move reports from the development machine
to the server, they lose properties, like font and can grow setttings. Please
help me identify the problem.
--
Thanks,
CGWNever mind... I was just missing a font on the other machine.
"CGW" wrote:
> We have a development machine, and a web server, both running SQL Reporting
> Services. My problem is that when I move reports from the development machine
> to the server, they lose properties, like font and can grow setttings. Please
> help me identify the problem.
> --
> Thanks,
> CGW

Property page giving error

Hi ,
I just deployed a report on a report Server from my Dev m/c...
The Report is running fine except the Property page for the Rpeort shows
the following error
"Unable to generate a temporary class (result=1). error CS1514: { expected
error CS1001: Identifier expected error CS1031: Type expected error CS1519:
Invalid token '{' in class, struct, or interface member declaration error
CS1514: { expected error CS1001: Identifier expected error CS1031: Type
expected error CS1519: Invalid token '{' in class, struct, or interface
member declaration "
I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
My Dev mc is Win XP.
BTW just found a bug with this version of the MSRS as the name of the rdl
file should not contain "Attachment" Key word else the Report can be deployed
but cant be viewed.
any Idea on above issue will be highly aprreciated.
siajDoes this happens to that particular report only?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
> Hi ,
> I just deployed a report on a report Server from my Dev m/c...
> The Report is running fine except the Property page for the Rpeort shows
> the following error
> "Unable to generate a temporary class (result=1). error CS1514: { expected
> error CS1001: Identifier expected error CS1031: Type expected error
> CS1519:
> Invalid token '{' in class, struct, or interface member declaration error
> CS1514: { expected error CS1001: Identifier expected error CS1031: Type
> expected error CS1519: Invalid token '{' in class, struct, or interface
> member declaration "
> I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
> My Dev mc is Win XP.
> BTW just found a bug with this version of the MSRS as the name of the rdl
> file should not contain "Attachment" Key word else the Report can be
> deployed
> but cant be viewed.
> any Idea on above issue will be highly aprreciated.
> siaj
>
>|||No it happens to all the Reports I deploy......
any suggestions will be highly appreciated..
siaj...
"Lev Semenets [MSFT]" wrote:
> Does this happens to that particular report only?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
> > Hi ,
> > I just deployed a report on a report Server from my Dev m/c...
> >
> > The Report is running fine except the Property page for the Rpeort shows
> > the following error
> > "Unable to generate a temporary class (result=1). error CS1514: { expected
> > error CS1001: Identifier expected error CS1031: Type expected error
> > CS1519:
> > Invalid token '{' in class, struct, or interface member declaration error
> > CS1514: { expected error CS1001: Identifier expected error CS1031: Type
> > expected error CS1519: Invalid token '{' in class, struct, or interface
> > member declaration "
> >
> > I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
> > My Dev mc is Win XP.
> >
> > BTW just found a bug with this version of the MSRS as the name of the rdl
> > file should not contain "Attachment" Key word else the Report can be
> > deployed
> > but cant be viewed.
> >
> > any Idea on above issue will be highly aprreciated.
> >
> > siaj
> >
> >
> >
> >
>
>|||Please check this related posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=41317637-24ab-414b-a650-6d1bb7e55085&sloc=en-us
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:38566414-72A9-4F5F-881D-290172AECBDC@.microsoft.com...
> No it happens to all the Reports I deploy......
> any suggestions will be highly appreciated..
> siaj...
>
> "Lev Semenets [MSFT]" wrote:
>> Does this happens to that particular report only?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "siaj" <siaj@.discussions.microsoft.com> wrote in message
>> news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
>> > Hi ,
>> > I just deployed a report on a report Server from my Dev m/c...
>> >
>> > The Report is running fine except the Property page for the Rpeort
>> > shows
>> > the following error
>> > "Unable to generate a temporary class (result=1). error CS1514: {
>> > expected
>> > error CS1001: Identifier expected error CS1031: Type expected error
>> > CS1519:
>> > Invalid token '{' in class, struct, or interface member declaration
>> > error
>> > CS1514: { expected error CS1001: Identifier expected error CS1031: Type
>> > expected error CS1519: Invalid token '{' in class, struct, or interface
>> > member declaration "
>> >
>> > I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000
>> > and
>> > My Dev mc is Win XP.
>> >
>> > BTW just found a bug with this version of the MSRS as the name of the
>> > rdl
>> > file should not contain "Attachment" Key word else the Report can be
>> > deployed
>> > but cant be viewed.
>> >
>> > any Idea on above issue will be highly aprreciated.
>> >
>> > siaj
>> >
>> >
>> >
>> >
>>

Property Owner is not available for Database '[DTA_TaxMan]'. This property may not exist

I get the following error everytime I try to go to the properties of
one of my databases.
The help link was useless.
I am not sure what to do. I am the top level administrator to my
server.
===================================
Cannot show requested dialog.
===================================
Cannot show requested dialog. (SqlMgmt)
Program Location:
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaun chFormHostedControlAllocator.AllocateDialog(XmlDoc ument
initializationXml, IServiceProvider dialogServiceProvider,
CDataContainer dc)
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaun chFormHostedControlAllocator.Microsoft.SqlServer.M anagement.SqlMgmt.ILaunchFormHostedControlAllocato r.CreateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm. InitializeForm(XmlDocument
doc, IServiceProvider provider, ISqlControlCollection control)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm. .ctor(XmlDocument
doc, IServiceProvider provider)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.ToolsMenuItem.OnCreateAndShowForm(ISe rviceProvider
sp, XmlDocument doc)
at
Microsoft.SqlServer.Management.SqlMgmt.RunningForm sTable.RunningFormsTableImpl.ThreadStarter.StartTh read()
===================================
Property Owner is not available for Database '[DTA_TaxMan]'. This
property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.PropertyCannotBeRetrievedExcept ionText&EvtID=Owner&LinkId=20476[/url]
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
index)
at Microsoft.SqlServer.Management.Smo.Database.get_Ow ner()
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype.DatabaseData..ctor( CDataContainer
context, String databaseName)
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype.LoadDefinition(Stri ng
newName)
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype..ctor(CDataContaine r
context)
at
Microsoft.SqlServer.Management.SqlManagerUI.DBProp Sheet..ctor(CDataContainer
context)
I have also signed back in with SA, and still having the same problem.
sql

Property Owner is not available for Database '[DTA_TaxMan]'. This property may not exist

I get the following error everytime I try to go to the properties of
one of my databases.
The help link was useless.
I am not sure what to do. I am the top level administrator to my
server.
===================================
Cannot show requested dialog.
===================================
Cannot show requested dialog. (SqlMgmt)
--
Program Location:
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider,
CDataContainer dc)
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument
doc, IServiceProvider provider, ISqlControlCollection control)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument
doc, IServiceProvider provider)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider
sp, XmlDocument doc)
at
Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()
===================================
Property Owner is not available for Database '[DTA_TaxMan]'. This
property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)
--
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
--
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32
index)
at Microsoft.SqlServer.Management.Smo.Database.get_Owner()
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer
context, String databaseName)
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String
newName)
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer
context)
at
Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer
context)I have also signed back in with SA, and still having the same problem.

Property Owner is not available for Database

I am getting this error while selecting the properties of one database.

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

Suddently this error started coming ? why ?

Make sure that the database owner is/was not a user / principal which has been deleted in the meantime. Orphaned database owner can be fixed using the sp_changedbowner and assigning a new owner to the database.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks..

I am getting this error while executing this.Do you know how to see the current/previous owner in studio ?

USE [TEST]

GO

EXEC sp_changedbowner 'dbo'

GO

Error

Msg 15151, Level 16, State 1, Line 1

Cannot find the principal 'dbo', because it does not exist or you do not have permission.

|||

You will have to provide a server prinicpal like 'sa'

To get the current owners, you will have to use something like the following:

SELECT databases.NAME,server_Principals.NAME

FROM sys.[databases]

INNER JOIN sys.[server_principals]

ON

[databases].owner_sid = [server_principals].sid

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

1) In this database all the tables/views owner is dbo.does that mean dbo should set as database owner ?

Your query is seleted only 7 databases Out of 15 databases . What is that mean ?

Can one database work with out principal login ? What is the default owner of non-system databases ?

Is there any code changes required if any database owner change ?

2) In studio , what is the difference between root--> security-->logins vs root>databases>TEST-->security--> users

Thanks

Julius

|||

That means that a macthing sid is not found int he server principals table, these database seem owner orphaned. Use a left join to find out which ones are not in there.

Databases can work without a principals owner but this should not be a normal state.

The default owner of newly created database is the server principal who created the database.

The are no code changes unless you use something like database ownership chaining.

2) The first path are the server principals (logins who have access to the server), the second path are the database principals (Server principals mapped to database principals) whoc have certain permissions on the database either through individual rights or group memebership.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:

> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>

Property IsPrimaryFile is not available for DataFile

I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
SQLServer 2005.
Then, when I try to access its properties I get:
Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
- I'm using a windows 2003 administrator account
- I tried setting compatibility level to 90 but it didn't help.
- If I connect with a sql-level user (not windows 2003 login) a can view the
properties, but I can't change anything.
Any ideas?
Thanks!http://www.dbtalk.net/microsoft-public-sqlserver-setup/copying-databse-sql-2000-sql-265404.html
"Gaspar" <Gaspar@.Gaspar.com> wrote in message
news:uOX2SbJaGHA.3612@.TK2MSFTNGP03.phx.gbl...
>I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
>SQLServer 2005.
> Then, when I try to access its properties I get:
> Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> - I'm using a windows 2003 administrator account
> - I tried setting compatibility level to 90 but it didn't help.
> - If I connect with a sql-level user (not windows 2003 login) a can view
> the properties, but I can't change anything.
> Any ideas?
> Thanks!
>

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:
> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>sql

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
Mukut
This may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:

> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>

Property IsPrimaryFile is not available for DataFile

I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
SQLServer 2005.
Then, when I try to access its properties I get:
Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'. Th
is
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
- I'm using a windows 2003 administrator account
- I tried setting compatibility level to 90 but it didn't help.
- If I connect with a sql-level user (not windows 2003 login) a can view the
properties, but I can't change anything.
Any ideas?
Thanks!http://www.dbtalk.net/microsoft-pub...
l-265404.html
"Gaspar" <Gaspar@.Gaspar.com> wrote in message
news:uOX2SbJaGHA.3612@.TK2MSFTNGP03.phx.gbl...
>I backup an SQL Server 2000 database (MY_DATABASE) and restore it in
>SQLServer 2005.
> Then, when I try to access its properties I get:
> Property IsPrimaryFile is not available for DataFile '[MY_DATABASE]'.
This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> - I'm using a windows 2003 administrator account
> - I tried setting compatibility level to 90 but it didn't help.
> - If I connect with a sql-level user (not windows 2003 login) a can view
> the properties, but I can't change anything.
> Any ideas?
> Thanks!
>

Property IsLocked is not available for Login '[sa]'

Hi everyone,
I'm at wits-end here and cannot figure out how in the world to show
the properties dialog window for the [sa] account (as well as one
other SQL account) in SQL Server 2005 Standard Edititon. This isn't
happening to all SQL accounts nor is it happening to any Windows
account on the server.
Below is the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsLocked is not available for Login '[sa]'. This property may
not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
--
BUTTONS:
OK
--
I've browsed the entire interweb (or at least the indexed pages of it
and every newsgroup I can think of) and have come to the conclusion
that this is one of the general errors that SQL 2005 kicks out when
things get good and screwed up.
Here are some futile attempts I've taken to remedy this error:
1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
'DamnYouStupidError' UNLOCK
2) Verified all databases had a valid account as an owner (all have sa
as an owner)
3) Verfied the sa account did not have rights to an old or nonexistent
database.
4) Verifed SP2 is installed, which it is
5) Pulled out my hair
6) Cussed in public... a lot
7) Posted to the newsgroups
I've basically wasted the better part of two days trying everything
under the sun to fix this, but I feel that I've gotten no where. If
anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
love to hear them.
Thanks,
JohnI have the same problem
Any solution about this?
Thanks
"John" <John.Eisbrener@.gmail.com> escribi en el mensaje
news:1177964038.783366.122620@.q75g2000hsh.googlegroups.com...
> Hi everyone,
> I'm at wits-end here and cannot figure out how in the world to show
> the properties dialog window for the [sa] account (as well as one
> other SQL account) in SQL Server 2005 Standard Edititon. This isn't
> happening to all SQL accounts nor is it happening to any Windows
> account on the server.
> Below is the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsLocked is not available for Login '[sa]'. This property may
> not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I've browsed the entire interweb (or at least the indexed pages of it
> and every newsgroup I can think of) and have come to the conclusion
> that this is one of the general errors that SQL 2005 kicks out when
> things get good and screwed up.
> Here are some futile attempts I've taken to remedy this error:
> 1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
> 'DamnYouStupidError' UNLOCK
> 2) Verified all databases had a valid account as an owner (all have sa
> as an owner)
> 3) Verfied the sa account did not have rights to an old or nonexistent
> database.
> 4) Verifed SP2 is installed, which it is
> 5) Pulled out my hair
> 6) Cussed in public... a lot
> 7) Posted to the newsgroups
> I've basically wasted the better part of two days trying everything
> under the sun to fix this, but I feel that I've gotten no where. If
> anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
> love to hear them.
> Thanks,
> John
>|||What operating system are you using? I'm having the same problem on Vista x6
4.
"John" wrote:

> Hi everyone,
> I'm at wits-end here and cannot figure out how in the world to show
> the properties dialog window for the [sa] account (as well as one
> other SQL account) in SQL Server 2005 Standard Edititon. This isn't
> happening to all SQL accounts nor is it happening to any Windows
> account on the server.
> Below is the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsLocked is not available for Login '[sa]'. This property may
> not exist for this object, or may not be retrievable due to
> insufficient access rights. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ed&LinkId=20476
> --
> BUTTONS:
> OK
> --
> I've browsed the entire interweb (or at least the indexed pages of it
> and every newsgroup I can think of) and have come to the conclusion
> that this is one of the general errors that SQL 2005 kicks out when
> things get good and screwed up.
> Here are some futile attempts I've taken to remedy this error:
> 1) Executed this in a query windows: ALTER LOGIN sa WITH PASSWORD =
> 'DamnYouStupidError' UNLOCK
> 2) Verified all databases had a valid account as an owner (all have sa
> as an owner)
> 3) Verfied the sa account did not have rights to an old or nonexistent
> database.
> 4) Verifed SP2 is installed, which it is
> 5) Pulled out my hair
> 6) Cussed in public... a lot
> 7) Posted to the newsgroups
> I've basically wasted the better part of two days trying everything
> under the sun to fix this, but I feel that I've gotten no where. If
> anyone has any suggestions shy of reinstalling SQL Server 2005, I'd
> love to hear them.
> Thanks,
> John
>|||I have Windows 2003 R2 64bits. I use SQL Server 2005 x64 in cluster.
Others problem around this are
Activity Monitor doesn't work. ("Unable to launch activity monitor. You may
not have sufficient permissions")
"Akhiris" <Akhiris@.discussions.microsoft.com> escribi en el mensaje
news:23AB87FD-8F62-4913-9DF6-53E45BCEC20C@.microsoft.com...[vbcol=seagreen]
> What operating system are you using? I'm having the same problem on Vista
> x64.
> "John" wrote:
>|||The only "solution" that I've found is to backup all of your
databases, uninstall SQL and reinstall it. Then restore the databases
and finally as a preventative measure, switch ownership of each
database to the dbo account. I'm still of the believe that there is a
different way to remedy this situation, but I haven't found anything
yet.
John
On May 31, 9:03 am, "Jose Cebrian" <jcebrian2...@.yahoo.es> wrote:
> I have the same problem
> Any solution about this?
> Thanks
> "John" <John.Eisbre...@.gmail.com> escribi=F3 en el mensajenews:1177964038=
.783366.122620@.q75g2000hsh.googlegroups.com...[vbcol=seagreen]
>
>
>
>
>
=3D...[vbcol=seagreen]
>
>
>
>
>
>

Property IsLocked is not available

I'm running Vista Ultimate x64 and when I tried to connect to my 2005 database using SQL server authentication it gave me an error. When I went to the management studio to check on the login it said this:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsLocked&LinkId=20476

BUTTONS:

OK

Has anyone ever seen this before and know how to solve it?

What build is the server running, and what build is your workstation running?|||Product: Microsoft SQL Server Developer Edition (64-bit)
Operating System: Microsoft Windows NT 6.0 (6000)
Version: 9.00.3054.00

|||Anybody?

I know others are having this problem. I've seen it on a couple of message boards.
|||

I ran into the same issue today when I installed an instance of SQL 2005 using Windows Auth. mode. After the install I switched the server over to SQL and Windows Auth mode and ran into the 'sa' problem. I ran the following command and then could see the properties of 'sa' within mgmt studio.

alter login sa

with password = 'yourpwd' unlock,

check_policy = off,

check_expiration = off

|||Works great... Except now I still can't log in with the sa account that I just unlocked from any machine.

TITLE: Connect to Server

Cannot connect to ANGEL.

ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

BUTTONS:

OK

|||You need to change the default database of the sa login. It should always be set to master to avoide this problem.

Property IsLocked is not available

I'm running Vista Ultimate x64 and when I tried to connect to my 2005 database using SQL server authentication it gave me an error. When I went to the management studio to check on the login it said this:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

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

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsLocked&LinkId=20476

BUTTONS:

OK

Has anyone ever seen this before and know how to solve it?

What build is the server running, and what build is your workstation running?|||Product: Microsoft SQL Server Developer Edition (64-bit)
Operating System: Microsoft Windows NT 6.0 (6000)
Version: 9.00.3054.00

|||Anybody?

I know others are having this problem. I've seen it on a couple of message boards.
|||

I ran into the same issue today when I installed an instance of SQL 2005 using Windows Auth. mode. After the install I switched the server over to SQL and Windows Auth mode and ran into the 'sa' problem. I ran the following command and then could see the properties of 'sa' within mgmt studio.

alterlogin sa

with password ='yourpwd' unlock,

check_policy =off,

check_expiration =off

|||Works great... Except now I still can't log in with the sa account that I just unlocked from any machine.

TITLE: Connect to Server

Cannot connect to ANGEL.

ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

BUTTONS:

OK

|||You need to change the default database of the sa login. It should always be set to master to avoide this problem.sql

Property DefaultSchema is not available for Database [DatabaseName] in SSMS

I've been researching the notification message [Property DefaultSchema is not available for Database [DatabaseName]]. This message is generated when I try to create a new table object using SSMS. I am logging into the SQL Server using an NT Authenticated Login which is mapped to an Active Directory Resource Group. I have found the following information specific to Default Schema's in SQL Server 2005.

First, by design, you cannot assign a Default Schema to an NT Authenticate Login that is mapped to a Windows Group. This is noted in the CREATE USER (Transact SQL) BOL topic - http://msdn2.microsoft.com/en-us/library/ms173463.aspx

"DEFAULT_SCHEMA cannot be specified when you are creating a user mapped to a Windows group, a certificate, or an asymmetric key."

QUESTION: Are there any plans to remove, or modify the query used to derive the Default Schema which is generating the message notification from SSMS?

SELECT (select default_schema_name from sys.database_principals where name = user_name()) AS [DefaultSchema]

This is quite frustrating for me, as I have to reply to my developers - by design you will receive this message when you try to create a table through the table editor using SSMS. I have also been informed that this same error is raised through VSTS for Database Developers.

It is a hassle, but you don't just have to tell people that they will get that error and cannot do anything about it. You just need to tell them that they must now specify the schema whenever they try to create a table.|||You'll encounter this error when you try to use the GUI to create a table, are not sysadmin, and your access to the SQL Server 2005 database is from a Windows group.

Property DefaultSchema is not available for Database [DatabaseName]

We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
Is this by design, a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users. I tried going through Server\Security\Logins and I got this error message

Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
Additional Information:
--> An exception occured while executing a Transact-Sql statement or batch.
(microsoft.SqlServer.ConnectionInfo)

--> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
with principals mapped to certificates or asymmetric keys.
(Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group? This would be more efficient then to add all the users to the Sql Server logins list.this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?|||I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database. The group and schema structure will usually be matched. Otherwise it will become difficult to manage security efficiently. Therefore it should be possible to assign a Default Schema to a group.
The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database. If the default schema for these users have to change, there is a high probability that some will be forgotten.
I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group. Why should this approach stop at SQL Server?|||

Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one.

Thanks
Laurentiu

|||Regarding this issue, I have a situation:

We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db.
The problem is when some developer (belong to group "A") tries to create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)".

We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier.

So, in this situation, how can I configure a specific network group to be db_owner (or have some other role)? Is it possible in SQL Server 2005? Don't tell me we have to configure all users, one by one...
In SQL Server 7/2000 we have no problem with this.

Tks.|||

I found the root cause of the problem you described and it seems to be a bug in SQL Server Management Studio when trying to create a schema-bound object on SQL Server 2005. The tool is internally trying to find (without success) the default schema for the user and failing.

What should happen (to have the same behavior found in SQL Server 2000) is that it should create an implicit user and schema for the caller (let’s call it user1 for simplicity) and the table in the specified schema (notice that the default schema name is the same as user name). It is important to observe that at the end you end up creating an implicit user and a schema for every member of the group that creates an object in the database.

In order to get the same results right now, you have two possible options:

1) Create the first object using TSQL directly:

a. Click on “New Query” and connect to the right server

b. Write the script to create the table and click on “Execute”, for example:

use [db_Test]

go

CREATE TABLE [MyTable]( data varchar(10) )

go

c. If you refresh the view in Object explorer you will notice the following changes:

i. Table created = [user1].[MyTable]

ii. New user = [Domain\user1]

iii. New schema = [Domain\user1]

NOTE: At this point you can drop this “dummy” table and everything should work fine from now on for this particular user.

2) Explicitly create a user for [Domain\user1], Schema [Domain\user1] is completely optional

IMPORTANT NOTE: To have an equivalent behavior, you will need to revoke the CONNECT permission on the database to [Domain\user1] (REVOKE CONNECT TO [Domain\user1]), this way the only access to the database would be via the Windows group membership and not by an explicit permission.

I hope we were able to help you, let us know if you have further questions. We really appreciate your feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Is MS going to release a fix for this so we can assign a default schema to a group?

|||

Hi,

I find this limitation a real pain in the ***.

This should be the same if you couldn't set a default db any more for a windows group. Because you have the same problem in this case if a user is a member of 2 different windows group and those both groups do have a login into SQL with 2 different default databases.

I know that you're asking for trouble at that moment. But I know that this is a situation to avoid.

The common sense need to be followed with the default schema on windows groups. You know the consequences if you don't make proper use of it.

I really hope that whit SP1 we can set a default schema to a windows group.

Greetings

Ludo

|||

I'm glad to see that my view on this issue is not unique. I previously file a request to include this possibility in a futur release and it was also turned down.

I believe the need is real and Microsoft should come up with a solution. I understand their point but also that it coul be overcome by putting in the proper validation and dialog boxes scenario. The people at Microsoft were able to upgrade Sql Server from "acceptable" to "great", but I believe that there is still place for improvements and the possibility to assign a default schema to windows group is one of them.

Keep adding your comments and we might be able to turn this around.

Thanks!

|||

This issue is already on our radar. It is actively tracked; we are not ignoring it. For your reference, the item that tracks this is 299032.

Thanks
Laurentiu

|||

Thanks Laurentiu, this is good news.

Gilles

|||

I glad to hear this.

This is very good news.

I hop it will be afvailable in SP1 or with a seperated hotfix.

Greetings

Ludo

|||

I agree this is a must add feature. We have a group of developers all developing the same database. We want to grant the AD group that they all belong to all necessary permissions and set their default schema to dbo. As it stands, we cannot set the default schema and have to create individual accounts for all of them.

***This negates the whole convenience of granting granular db permissions to the AD group.***

Please fix this as it is very frustrating.

|||Indeed!!|||Can you please provide a link to the MS item number that you reference above?

Property DefaultSchema is not available for Database [DatabaseName]

We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
Is this by design, a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users. I tried going through Server\Security\Logins and I got this error message

Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
Additional Information:
--> An exception occured while executing a Transact-Sql statement or batch.
(microsoft.SqlServer.ConnectionInfo)

--> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
with principals mapped to certificates or asymmetric keys.
(Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group? This would be more efficient then to add all the users to the Sql Server logins list.this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?|||I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database. The group and schema structure will usually be matched. Otherwise it will become difficult to manage security efficiently. Therefore it should be possible to assign a Default Schema to a group.
The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database. If the default schema for these users have to change, there is a high probability that some will be forgotten.
I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group. Why should this approach stop at SQL Server?|||

Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one.

Thanks
Laurentiu

|||Regarding this issue, I have a situation:

We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db.
The problem is when some developer (belong to group "A") tries to create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)".

We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier.

So, in this situation, how can I configure a specific network group to be db_owner (or have some other role)? Is it possible in SQL Server 2005? Don't tell me we have to configure all users, one by one...
In SQL Server 7/2000 we have no problem with this.

Tks.
|||

I found the root cause of the problem you described and it seems to be a bug in SQL Server Management Studio when trying to create a schema-bound object on SQL Server 2005. The tool is internally trying to find (without success) the default schema for the user and failing.

What should happen (to have the same behavior found in SQL Server 2000) is that it should create an implicit user and schema for the caller (let’s call it user1 for simplicity) and the table in the specified schema (notice that the default schema name is the same as user name). It is important to observe that at the end you end up creating an implicit user and a schema for every member of the group that creates an object in the database.

In order to get the same results right now, you have two possible options:

1) Create the first object using TSQL directly:

a. Click on “New Query” and connect to the right server

b. Write the script to create the table and click on “Execute”, for example:

use [db_Test]

go

CREATE TABLE [MyTable]( data varchar(10) )

go

c. If you refresh the view in Object explorer you will notice the following changes:

i. Table created = [user1].[MyTable]

ii. New user = [Domain\user1]

iii. New schema = [Domain\user1]

NOTE: At this point you can drop this “dummy” table and everything should work fine from now on for this particular user.

2) Explicitly create a user for [Domain\user1], Schema [Domain\user1] is completely optional

IMPORTANT NOTE: To have an equivalent behavior, you will need to revoke the CONNECT permission on the database to [Domain\user1] (REVOKE CONNECT TO [Domain\user1]), this way the only access to the database would be via the Windows group membership and not by an explicit permission.

I hope we were able to help you, let us know if you have further questions. We really appreciate your feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Is MS going to release a fix for this so we can assign a default schema to a group?

|||

Hi,

I find this limitation a real pain in the ***.

This should be the same if you couldn't set a default db any more for a windows group. Because you have the same problem in this case if a user is a member of 2 different windows group and those both groups do have a login into SQL with 2 different default databases.

I know that you're asking for trouble at that moment. But I know that this is a situation to avoid.

The common sense need to be followed with the default schema on windows groups. You know the consequences if you don't make proper use of it.

I really hope that whit SP1 we can set a default schema to a windows group.

Greetings

Ludo

|||

I'm glad to see that my view on this issue is not unique. I previously file a request to include this possibility in a futur release and it was also turned down.

I believe the need is real and Microsoft should come up with a solution. I understand their point but also that it coul be overcome by putting in the proper validation and dialog boxes scenario. The people at Microsoft were able to upgrade Sql Server from "acceptable" to "great", but I believe that there is still place for improvements and the possibility to assign a default schema to windows group is one of them.

Keep adding your comments and we might be able to turn this around.

Thanks!

|||

This issue is already on our radar. It is actively tracked; we are not ignoring it. For your reference, the item that tracks this is 299032.

Thanks
Laurentiu

|||

Thanks Laurentiu, this is good news.

Gilles

|||

I glad to hear this.

This is very good news.

I hop it will be afvailable in SP1 or with a seperated hotfix.

Greetings

Ludo

|||

I agree this is a must add feature. We have a group of developers all developing the same database. We want to grant the AD group that they all belong to all necessary permissions and set their default schema to dbo. As it stands, we cannot set the default schema and have to create individual accounts for all of them.

***This negates the whole convenience of granting granular db permissions to the AD group.***

Please fix this as it is very frustrating.

|||Indeed!!|||Can you please provide a link to the MS item number that you reference above?

Property DefaultSchema is not available for Database [DatabaseName]

We're using Windows Authentication with our SQL Server. We've added a domain group to the SQL Server groups and would like to give it a default schema. The properties window is the same for users and groups but the default schema field is enable only for user entities. We cannot add a default schema to a group.
Is this by design, a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users. I tried going through Server\Security\Logins and I got this error message

Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
Additional Information:
--> An exception occured while executing a Transact-Sql statement or batch.
(microsoft.SqlServer.ConnectionInfo)

--> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
with principals mapped to certificates or asymmetric keys.
(Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group? This would be more efficient then to add all the users to the Sql Server logins list.this is by design. Group cannot have a default schema, since no principal can have group as its primary id (as opposite to group id). Consider following: windows user1 is added to windows group1 and group2. If both of them are allowed to have default schema what default schema user1 has then?|||I do understand the situation you described but in a well design group structure, a user will exist in only one group for a specific database. The group and schema structure will usually be matched. Otherwise it will become difficult to manage security efficiently. Therefore it should be possible to assign a Default Schema to a group.
The way it is now, if you have 100 users in a specific group instead of having to add only that group with a Default Schema, we now have to manage 100 different users within the database. If the default schema for these users have to change, there is a high probability that some will be forgotten.
I believe Microsoft has a good approach to security by using Domain\Group to which we give privileges and then we had the targeted users to the group. Why should this approach stop at SQL Server?|||

Unfortunately, we cannot design a solution assuming that group structures will be well designed and that users can be members of at most one group in any database. So, in SQL Server 2005, setting a default schema for a group is not supported. The problem is that a default schema is a property, not a privilege, and is therefore not cumulative. Given two different default schemas, we cannot predictably choose one.

Thanks
Laurentiu

|||Regarding this issue, I have a situation:

We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db.
The problem is when some developer (belong to group "A") tries to create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)".

We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier.

So, in this situation, how can I configure a specific network group to be db_owner (or have some other role)? Is it possible in SQL Server 2005? Don't tell me we have to configure all users, one by one...
In SQL Server 7/2000 we have no problem with this.

Tks.
|||

I found the root cause of the problem you described and it seems to be a bug in SQL Server Management Studio when trying to create a schema-bound object on SQL Server 2005. The tool is internally trying to find (without success) the default schema for the user and failing.

What should happen (to have the same behavior found in SQL Server 2000) is that it should create an implicit user and schema for the caller (let’s call it user1 for simplicity) and the table in the specified schema (notice that the default schema name is the same as user name). It is important to observe that at the end you end up creating an implicit user and a schema for every member of the group that creates an object in the database.

In order to get the same results right now, you have two possible options:

1) Create the first object using TSQL directly:

a. Click on “New Query” and connect to the right server

b. Write the script to create the table and click on “Execute”, for example:

use [db_Test]

go

CREATE TABLE [MyTable]( data varchar(10) )

go

c. If you refresh the view in Object explorer you will notice the following changes:

i. Table created = [user1].[MyTable]

ii. New user = [Domain\user1]

iii. New schema = [Domain\user1]

NOTE: At this point you can drop this “dummy” table and everything should work fine from now on for this particular user.

2) Explicitly create a user for [Domain\user1], Schema [Domain\user1] is completely optional

IMPORTANT NOTE: To have an equivalent behavior, you will need to revoke the CONNECT permission on the database to [Domain\user1] (REVOKE CONNECT TO [Domain\user1]), this way the only access to the database would be via the Windows group membership and not by an explicit permission.

I hope we were able to help you, let us know if you have further questions. We really appreciate your feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Is MS going to release a fix for this so we can assign a default schema to a group?

|||

Hi,

I find this limitation a real pain in the ***.

This should be the same if you couldn't set a default db any more for a windows group. Because you have the same problem in this case if a user is a member of 2 different windows group and those both groups do have a login into SQL with 2 different default databases.

I know that you're asking for trouble at that moment. But I know that this is a situation to avoid.

The common sense need to be followed with the default schema on windows groups. You know the consequences if you don't make proper use of it.

I really hope that whit SP1 we can set a default schema to a windows group.

Greetings

Ludo

|||

I'm glad to see that my view on this issue is not unique. I previously file a request to include this possibility in a futur release and it was also turned down.

I believe the need is real and Microsoft should come up with a solution. I understand their point but also that it coul be overcome by putting in the proper validation and dialog boxes scenario. The people at Microsoft were able to upgrade Sql Server from "acceptable" to "great", but I believe that there is still place for improvements and the possibility to assign a default schema to windows group is one of them.

Keep adding your comments and we might be able to turn this around.

Thanks!

|||

This issue is already on our radar. It is actively tracked; we are not ignoring it. For your reference, the item that tracks this is 299032.

Thanks
Laurentiu

|||

Thanks Laurentiu, this is good news.

Gilles

|||

I glad to hear this.

This is very good news.

I hop it will be afvailable in SP1 or with a seperated hotfix.

Greetings

Ludo

|||

I agree this is a must add feature. We have a group of developers all developing the same database. We want to grant the AD group that they all belong to all necessary permissions and set their default schema to dbo. As it stands, we cannot set the default schema and have to create individual accounts for all of them.

***This negates the whole convenience of granting granular db permissions to the AD group.***

Please fix this as it is very frustrating.

|||Indeed!!|||Can you please provide a link to the MS item number that you reference above?