permissions gone missing in sysprotects

Hi all,
I have upgarded db from sql 7.0 to sql 2000 and I notice that load of
records have gone missing in sysprotects table ( something like from 5000
records down to 60 records only).
As a results, permission setting does not appear in objects permission
management in Enterprise Manager.
I know that the permission setting is still hold in syspermissions table,
but without the "green ticks" appears on the Enterprise manager interface, w
e
would not be able to know what permission we have assigned to each role or
user at all.
This problem only happen with the database that has been moved from 7.0 to
2000 though , if you move db from 2000 to 2000, it seems to be fine.
Does anyone else out there know how to fix this ?
cheers
JackPlease take a look at the following kb, especially at the transfer logins
section.
http://support.microsoft.com/kb/314546
-oj
"Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
news:2FEBE409-12C4-48D0-B076-5D5EAF607534@.microsoft.com...
> Hi all,
> I have upgarded db from sql 7.0 to sql 2000 and I notice that load of
> records have gone missing in sysprotects table ( something like from 5000
> records down to 60 records only).
> As a results, permission setting does not appear in objects permission
> management in Enterprise Manager.
> I know that the permission setting is still hold in syspermissions table,
> but without the "green ticks" appears on the Enterprise manager interface,
> we
> would not be able to know what permission we have assigned to each role or
> user at all.
> This problem only happen with the database that has been moved from 7.0 to
> 2000 though , if you move db from 2000 to 2000, it seems to be fine.
> Does anyone else out there know how to fix this ?
> cheers
> Jack
>|||Hi oj,
this is not orphan user problem because it even happens with the newly
created user. What happen is that , when I assigned permission setting to a
user (newly created or existing users, it don't matter) , the green ticks
don't hold, so when I go back to look at permission setting, they are all
blank, no green ticks indicate what permission was setting before.
However, the permission I assigned does work. A user cannot perform any task
(select, insert, etc) that is not allowed to.
The real problem of this "green ticks don't stay" issue is not about
permission, it is about "permission management".
Because I cannot see the "green ticks" , there is no way for me to know [or
to remember] what permission have I been assigned to roles, users.
Using "sp_helprotect" does not help either. Because sp_helprotect does get
results from sysprotects, but the permission I set only went into
"syspermissions", but not to "sysprotects".
Please note again, this "phenomenon" only occurs when one migrate database
from SQL 7.0 to SQL 2000. Migrate DB from SQL 2000 to SQL 2000 is fine.
I was wondering if any Microsoft SQL guru out there actually ever come
across this problem before, but I must admit that I am surprised I cannot
find any document that talk directly about how to solve this matter at all i
n
MSDN or Books on line or not even the entire web (search using google).
Any suggestions would be appreciated
Jack
"oj" wrote:

> Please take a look at the following kb, especially at the transfer logins
> section.
> http://support.microsoft.com/kb/314546
>
> --
> -oj
>
> "Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
> news:2FEBE409-12C4-48D0-B076-5D5EAF607534@.microsoft.com...
>
>|||Hello Jack,
I currently do not have access to a sql7 to confirm. Though, I've seen this
in the past when I upgraded to sql2k. At this point, I would suggest firing
up Profiler and see what is sent to the backend when you use EM to
change/set permission.
G'luck.
--
-oj
"Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
news:A3913B95-5F42-44D4-A8E4-A15E8A99D2C8@.microsoft.com...
> Hi oj,
> this is not orphan user problem because it even happens with the newly
> created user. What happen is that , when I assigned permission setting to
> a
> user (newly created or existing users, it don't matter) , the green ticks
> don't hold, so when I go back to look at permission setting, they are all
> blank, no green ticks indicate what permission was setting before.
> However, the permission I assigned does work. A user cannot perform any
> task
> (select, insert, etc) that is not allowed to.
> The real problem of this "green ticks don't stay" issue is not about
> permission, it is about "permission management".
> Because I cannot see the "green ticks" , there is no way for me to know
> [or
> to remember] what permission have I been assigned to roles, users.
> Using "sp_helprotect" does not help either. Because sp_helprotect does
> get
> results from sysprotects, but the permission I set only went into
> "syspermissions", but not to "sysprotects".
> Please note again, this "phenomenon" only occurs when one migrate database
> from SQL 7.0 to SQL 2000. Migrate DB from SQL 2000 to SQL 2000 is fine.
> I was wondering if any Microsoft SQL guru out there actually ever come
> across this problem before, but I must admit that I am surprised I cannot
> find any document that talk directly about how to solve this matter at all
> in
> MSDN or Books on line or not even the entire web (search using google).
> Any suggestions would be appreciated
> Jack
> "oj" wrote:
>|||Hi Oj,
yes, I did fire up profiler, that is why I can tell that the permission
setting did not go into sysprotects but only go into syspermissions.
And even if I can see how EM insert permission record into system tables,
nothing I can do to change the statement to make it sync both sysprotects an
d
syspermissions (which is what it normally does).
I can manully hack into sysprotects table to insert the missing records to
make it match up with sysusers, syspermissions and sysobjects, but that is
not the solution anyway, because I would then have to do it everytime I work
on user's permission.
any clue ?
Jack
"oj" wrote:

> Hello Jack,
> I currently do not have access to a sql7 to confirm. Though, I've seen thi
s
> in the past when I upgraded to sql2k. At this point, I would suggest firin
g
> up Profiler and see what is sent to the backend when you use EM to
> change/set permission.
> G'luck.
> --
> -oj
>
> "Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
> news:A3913B95-5F42-44D4-A8E4-A15E8A99D2C8@.microsoft.com...
>
>|||First, I mispoke on my last comment. I should have said "I have not seen".
Anyway, do sp_dbcmptlevel on the upgraded database to see if it's set to 80.
I really can't think of a good reason for it not to insert into sysprotects
if it's upgraded from sql7 and its current compatibility level is set to 80.
If needs to, please give PSS a call. You won't be charged if it's a bug.
-oj
"Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
news:19F1D9C1-0324-4F82-B26E-99A4BC0AC99B@.microsoft.com...
> Hi Oj,
> yes, I did fire up profiler, that is why I can tell that the permission
> setting did not go into sysprotects but only go into syspermissions.
> And even if I can see how EM insert permission record into system tables,
> nothing I can do to change the statement to make it sync both sysprotects
> and
> syspermissions (which is what it normally does).
> I can manully hack into sysprotects table to insert the missing records to
> make it match up with sysusers, syspermissions and sysobjects, but that is
> not the solution anyway, because I would then have to do it everytime I
> work
> on user's permission.
> any clue ?
> Jack
>
> "oj" wrote:
>|||what is PSS ?
"oj" wrote:

> First, I mispoke on my last comment. I should have said "I have not seen".
> Anyway, do sp_dbcmptlevel on the upgraded database to see if it's set to 8
0.
> I really can't think of a good reason for it not to insert into sysprotect
s
> if it's upgraded from sql7 and its current compatibility level is set to 8
0.
> If needs to, please give PSS a call. You won't be charged if it's a bug.
> --
> -oj
>
> "Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
> news:19F1D9C1-0324-4F82-B26E-99A4BC0AC99B@.microsoft.com...
>
>|||Jack,
It's MS Product Support Services.
For SQL:
http://support.microsoft.com/oas/de...392&gprid=36498
-oj
"Jack Yao" <JackYao@.discussions.microsoft.com> wrote in message
news:85998831-E11F-41F9-B6ED-1EAC3176432C@.microsoft.com...
> what is PSS ?
> "oj" wrote:
>