move db from sql2000 to 2005sp1

I have sql2000 db that had two domain groups in the dbo role and one SQL
acct also in the dbo role. There are only tables in the db and all owned by
dbo, I am not sure if the developers created the tables and then changed the
owner or specified on creation. I restored the db to sql2005sp1 and setup
the logins via the sp_helprevlogins and now crap has hit the fan.
1) The domain group can login to SQL but when one of them clicks on the
user tables to expand they get the error:
Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
Additional Information:
Am exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.sqlserver.connectioninfo)
Select permission denied on object 'extended properties', database
mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
I know the one that is having the issue is the one that created the tables.
Others in the domain group can see the tables.
2) Others in group that can see the table, when they try and create a new
table and get the following error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I guess I am missing something.
Thanks
CHi
It sounds like your upgrade procedure has not given the users the correct
permissions. I would expect all users within the same domain group to have
the same problems, therefore Iif they get different problems they are
probably in different groups.
Do you really want to grant such wide permissions to so many users? If you
created a database role with the minimum number of permissions you require
then the domain group could be added to that. In which case dropping the
domain group as a login and adding it again would not be an issue.
You may want to use GRANT LOGIN when you add the groups rather than
sp_addlogin.
John
"CD" wrote:
> I have sql2000 db that had two domain groups in the dbo role and one SQL
> acct also in the dbo role. There are only tables in the db and all owned by
> dbo, I am not sure if the developers created the tables and then changed the
> owner or specified on creation. I restored the db to sql2005sp1 and setup
> the logins via the sp_helprevlogins and now crap has hit the fan.
>
> 1) The domain group can login to SQL but when one of them clicks on the
> user tables to expand they get the error:
>
> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> Additional Information:
> Am exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.sqlserver.connectioninfo)
> Select permission denied on object 'extended properties', database
> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>
> I know the one that is having the issue is the one that created the tables.
> Others in the domain group can see the tables.
>
> 2) Others in group that can see the table, when they try and create a new
> table and get the following error:
>
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
>
>
> I guess I am missing something.
>
> Thanks
> C
>
>|||Thanks for the reply, no actually the group constist of 3 domain accts with
the group being the database pre-defined dbo role, only one of them can not
see the table objects. I know this one created the tables on the sql2000
side and then changed the owner of the tables to dbo (error 1 note). The
other 2 accounts can see all the tables but can not create a new one.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> Hi
> It sounds like your upgrade procedure has not given the users the correct
> permissions. I would expect all users within the same domain group to have
> the same problems, therefore Iif they get different problems they are
> probably in different groups.
> Do you really want to grant such wide permissions to so many users? If you
> created a database role with the minimum number of permissions you require
> then the domain group could be added to that. In which case dropping the
> domain group as a login and adding it again would not be an issue.
> You may want to use GRANT LOGIN when you add the groups rather than
> sp_addlogin.
> John
> "CD" wrote:
>> I have sql2000 db that had two domain groups in the dbo role and one SQL
>> acct also in the dbo role. There are only tables in the db and all owned
>> by
>> dbo, I am not sure if the developers created the tables and then changed
>> the
>> owner or specified on creation. I restored the db to sql2005sp1 and
>> setup
>> the logins via the sp_helprevlogins and now crap has hit the fan.
>>
>> 1) The domain group can login to SQL but when one of them clicks on the
>> user tables to expand they get the error:
>>
>> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
>> Additional Information:
>> Am exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.sqlserver.connectioninfo)
>> Select permission denied on object 'extended properties', database
>> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
>>
>> I know the one that is having the issue is the one that created the
>> tables.
>> Others in the domain group can see the tables.
>>
>> 2) Others in group that can see the table, when they try and create a new
>> table and get the following error:
>>
>> Property DefaultSchema is not available for the Database '[XXXXDB]'.
>> This
>> property may not exist for this object, or may not be retrievable due to
>> insufficient access rights. (sqleditors)
>>
>>
>> I guess I am missing something.
>>
>> Thanks
>> C
>>|||Hi
If there are no permissions granted directly to the group then you should
not have an issue dropping and re-creating it. The logins would not require
their own schemas. To create tables on the schemas they would require CREATE
TABLE permission on the database and ALTER permission on the schema. You
would require other permissions if you started to use XML schemas.
John
"CD" wrote:
> Thanks for the reply, no actually the group constist of 3 domain accts with
> the group being the database pre-defined dbo role, only one of them can not
> see the table objects. I know this one created the tables on the sql2000
> side and then changed the owner of the tables to dbo (error 1 note). The
> other 2 accounts can see all the tables but can not create a new one.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9A9CE67E-59B0-4A5B-A797-4F02FCCD9A7C@.microsoft.com...
> > Hi
> >
> > It sounds like your upgrade procedure has not given the users the correct
> > permissions. I would expect all users within the same domain group to have
> > the same problems, therefore Iif they get different problems they are
> > probably in different groups.
> >
> > Do you really want to grant such wide permissions to so many users? If you
> > created a database role with the minimum number of permissions you require
> > then the domain group could be added to that. In which case dropping the
> > domain group as a login and adding it again would not be an issue.
> >
> > You may want to use GRANT LOGIN when you add the groups rather than
> > sp_addlogin.
> >
> > John
> >
> > "CD" wrote:
> >
> >> I have sql2000 db that had two domain groups in the dbo role and one SQL
> >> acct also in the dbo role. There are only tables in the db and all owned
> >> by
> >> dbo, I am not sure if the developers created the tables and then changed
> >> the
> >> owner or specified on creation. I restored the db to sql2005sp1 and
> >> setup
> >> the logins via the sp_helprevlogins and now crap has hit the fan.
> >>
> >>
> >>
> >> 1) The domain group can login to SQL but when one of them clicks on the
> >> user tables to expand they get the error:
> >>
> >>
> >>
> >> Failed to retrieve data for this request (Microsoft.sql.server.smoenum)
> >>
> >> Additional Information:
> >>
> >> Am exception occurred while executing a Transact-SQL statement or batch.
> >>
> >> (Microsoft.sqlserver.connectioninfo)
> >>
> >> Select permission denied on object 'extended properties', database
> >> mssqlsystemresource', schema 'sys'.(Microsoft sql server, error 329)
> >>
> >>
> >>
> >> I know the one that is having the issue is the one that created the
> >> tables.
> >> Others in the domain group can see the tables.
> >>
> >>
> >>
> >> 2) Others in group that can see the table, when they try and create a new
> >> table and get the following error:
> >>
> >>
> >>
> >> Property DefaultSchema is not available for the Database '[XXXXDB]'.
> >> This
> >> property may not exist for this object, or may not be retrievable due to
> >> insufficient access rights. (sqleditors)
> >>
> >>
> >>
> >>
> >>
> >> I guess I am missing something.
> >>
> >>
> >>
> >> Thanks
> >>
> >> C
> >>
> >>
> >>
>
>|||I am feeling stupid here.
But I need to get the domain\DevTeam rights to create a table. They can
alter or delete an existing but not create they get the error:
Property DefaultSchema is not available for the Database '[XXXXDB]'. This
property may not exist for this object, or may not be retrievable due to
insufficient access rights. (sqleditors)
I tried at the DB permission level setting what you desribed but get the
same error.
What am I missing here?
1) I create the user database.
2) add the group to at the SQL/Security/Logins level
2a) then on the User Mapping, check the Map to the database on the top,
check the database role of db_owner at the bottom.
What else?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> Hi
> If there are no permissions granted directly to the group then you should
> not have an issue dropping and re-creating it. The logins would not
> require
> their own schemas. To create tables on the schemas they would require
> CREATE
> TABLE permission on the database and ALTER permission on the schema. You
> would require other permissions if you started to use XML schemas.
> John|||Hi
You can not define a default schema for a windows group and this seems to
stop you using the New Table wizard in Managment Studio. You will be able to
create tables from a query window and specify the schema as dbo, therefore
eliminating any need to change the schema later. Using a role, which has the
permissions I stated in the last post then a warning will appear when you
enter the new table dialog, but it will not create the tables in the dbo
schema.
John
"CD" wrote:
> I am feeling stupid here.
> But I need to get the domain\DevTeam rights to create a table. They can
> alter or delete an existing but not create they get the error:
> Property DefaultSchema is not available for the Database '[XXXXDB]'. This
> property may not exist for this object, or may not be retrievable due to
> insufficient access rights. (sqleditors)
> I tried at the DB permission level setting what you desribed but get the
> same error.
> What am I missing here?
> 1) I create the user database.
> 2) add the group to at the SQL/Security/Logins level
> 2a) then on the User Mapping, check the Map to the database on the top,
> check the database role of db_owner at the bottom.
> What else?
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:6BA2DFCA-32EF-42F5-89BB-ED26646AE4F4@.microsoft.com...
> > Hi
> >
> > If there are no permissions granted directly to the group then you should
> > not have an issue dropping and re-creating it. The logins would not
> > require
> > their own schemas. To create tables on the schemas they would require
> > CREATE
> > TABLE permission on the database and ALTER permission on the schema. You
> > would require other permissions if you started to use XML schemas.
> >
> > John
>
>