How to fix dbcc checkcatalog error (Msg 3853)

After upgrading two of my SQL2000 databases to SQL2005, I see the following
error when running dbcc checkdb (which runs dbcc checkcatalog):
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of row
(role_principal_id=16384,member_principal_id=1) in sys.database_role_members
does not have a matching row (principal_id=16384) in sys.database_principals.
From what I've read, dbcc checkcatalog errors cannot be fixed automatically.
Usually you have to re-create the object. But most of what I've seen on this
error refers to tables and stored procedures, things that are more easily
re-creatable.
How do I figure out what role is causing the problem and then how can I
resolve the error?
Thanks.I haven't seen this error before but it seems to indicate that the db_owner
role is missing. Do you see db_owner when you execute:
SELECT * FROM sys.database_principals
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> After upgrading two of my SQL2000 databases to SQL2005, I see the
> following
> error when running dbcc checkdb (which runs dbcc checkcatalog):
> Msg 8992, Level 16, State 1, Line 1
> Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> row
> (role_principal_id=16384,member_principal_id=1) in
> sys.database_role_members
> does not have a matching row (principal_id=16384) in
> sys.database_principals.
> From what I've read, dbcc checkcatalog errors cannot be fixed
> automatically.
> Usually you have to re-create the object. But most of what I've seen on
> this
> error refers to tables and stored procedures, things that are more easily
> re-creatable.
> How do I figure out what role is causing the problem and then how can I
> resolve the error?
> Thanks.|||You're right ... all of the db_* roles seem to be missing. I upgraded three
SQL2000 databases using the detach/attach method. One database has all the
proper roles (and db_owner is principal_id 16384) but the other two are
missing all of the 16xxx principal_id roles.
The databases seem to be working fine (they are for PeopleSoft applications)
even without those roles.
What can I do to fix this?
"Dan Guzman" wrote:
> I haven't seen this error before but it seems to indicate that the db_owner
> role is missing. Do you see db_owner when you execute:
> SELECT * FROM sys.database_principals
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> > After upgrading two of my SQL2000 databases to SQL2005, I see the
> > following
> > error when running dbcc checkdb (which runs dbcc checkcatalog):
> >
> > Msg 8992, Level 16, State 1, Line 1
> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> > row
> > (role_principal_id=16384,member_principal_id=1) in
> > sys.database_role_members
> > does not have a matching row (principal_id=16384) in
> > sys.database_principals.
> >
> > From what I've read, dbcc checkcatalog errors cannot be fixed
> > automatically.
> > Usually you have to re-create the object. But most of what I've seen on
> > this
> > error refers to tables and stored procedures, things that are more easily
> > re-creatable.
> >
> > How do I figure out what role is causing the problem and then how can I
> > resolve the error?
> >
> > Thanks.
>
>|||> What can I do to fix this?
In the good ole days, I'd have recommended some at-your-own-risk system
table hacking. This isn't allowed in SQL 2005 so your only options are to
either contact Microsoft support or copy your users and objects to new
databases you know to be good.
It would be nice to know how this happened. If you still have old copies of
the SQL 2000 db files around, that might help diagnosis.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...
> You're right ... all of the db_* roles seem to be missing. I upgraded
> three
> SQL2000 databases using the detach/attach method. One database has all the
> proper roles (and db_owner is principal_id 16384) but the other two are
> missing all of the 16xxx principal_id roles.
> The databases seem to be working fine (they are for PeopleSoft
> applications)
> even without those roles.
> What can I do to fix this?
> "Dan Guzman" wrote:
>> I haven't seen this error before but it seems to indicate that the
>> db_owner
>> role is missing. Do you see db_owner when you execute:
>> SELECT * FROM sys.database_principals
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
>> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
>> > After upgrading two of my SQL2000 databases to SQL2005, I see the
>> > following
>> > error when running dbcc checkdb (which runs dbcc checkcatalog):
>> >
>> > Msg 8992, Level 16, State 1, Line 1
>> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
>> > row
>> > (role_principal_id=16384,member_principal_id=1) in
>> > sys.database_role_members
>> > does not have a matching row (principal_id=16384) in
>> > sys.database_principals.
>> >
>> > From what I've read, dbcc checkcatalog errors cannot be fixed
>> > automatically.
>> > Usually you have to re-create the object. But most of what I've seen on
>> > this
>> > error refers to tables and stored procedures, things that are more
>> > easily
>> > re-creatable.
>> >
>> > How do I figure out what role is causing the problem and then how can I
>> > resolve the error?
>> >
>> > Thanks.
>>|||I ended up re-creating the database from scratch and then manually reloaded
my tables. That seems to have resolved the problem: all of the proper roles
are there and the dbcc checkcatalog completes successfully.
Thanks.
"Dan Guzman" wrote:
> > What can I do to fix this?
> In the good ole days, I'd have recommended some at-your-own-risk system
> table hacking. This isn't allowed in SQL 2005 so your only options are to
> either contact Microsoft support or copy your users and objects to new
> databases you know to be good.
> It would be nice to know how this happened. If you still have old copies of
> the SQL 2000 db files around, that might help diagnosis.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> news:200040DC-E547-41DB-B358-88D041ADEC4F@.microsoft.com...
> > You're right ... all of the db_* roles seem to be missing. I upgraded
> > three
> > SQL2000 databases using the detach/attach method. One database has all the
> > proper roles (and db_owner is principal_id 16384) but the other two are
> > missing all of the 16xxx principal_id roles.
> >
> > The databases seem to be working fine (they are for PeopleSoft
> > applications)
> > even without those roles.
> >
> > What can I do to fix this?
> >
> > "Dan Guzman" wrote:
> >
> >> I haven't seen this error before but it seems to indicate that the
> >> db_owner
> >> role is missing. Do you see db_owner when you execute:
> >>
> >> SELECT * FROM sys.database_principals
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Jeremy" <Jeremy@.discussions.microsoft.com> wrote in message
> >> news:275F4F6A-B222-4317-A132-F716D065E6CF@.microsoft.com...
> >> > After upgrading two of my SQL2000 databases to SQL2005, I see the
> >> > following
> >> > error when running dbcc checkdb (which runs dbcc checkcatalog):
> >> >
> >> > Msg 8992, Level 16, State 1, Line 1
> >> > Check Catalog Msg 3853, State 1: Attribute (role_principal_id=16384) of
> >> > row
> >> > (role_principal_id=16384,member_principal_id=1) in
> >> > sys.database_role_members
> >> > does not have a matching row (principal_id=16384) in
> >> > sys.database_principals.
> >> >
> >> > From what I've read, dbcc checkcatalog errors cannot be fixed
> >> > automatically.
> >> > Usually you have to re-create the object. But most of what I've seen on
> >> > this
> >> > error refers to tables and stored procedures, things that are more
> >> > easily
> >> > re-creatable.
> >> >
> >> > How do I figure out what role is causing the problem and then how can I
> >> > resolve the error?
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>