permissions gone missing in sysprotects

Hi there,
I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
noticed 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
JackI have never heard of this one.
One way I could see having problems is if you had modified
system tables. That's the only scenario I can think of but I
would have thought you'd get an error in the upgrade
process.
As already suggested in one of the other groups, you should
contact Microsoft Product Support if you feel you have a
bug.
-Sue
On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
<JackYao@.discussions.microsoft.com> wrote:

>Hi there,
>I have found the bugs when upgarded DB from sql 7.0 to sql 2000 and I
>noticed 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,
By any chance were these permissions on system objects in master.dbo?
AFAIK these were never preserved during upgrades.
Note - In Yukon, we now preserve permissions on system objects during the
upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshell
,
we will remember that during the upgrade.
Regards,
Clifford Dibble
Program Manager
SQL Server Engine
"Sue Hoegemeier" wrote:

> I have never heard of this one.
> One way I could see having problems is if you had modified
> system tables. That's the only scenario I can think of but I
> would have thought you'd get an error in the upgrade
> process.
> As already suggested in one of the other groups, you should
> contact Microsoft Product Support if you feel you have a
> bug.
> -Sue
> On Wed, 26 Jan 2005 20:45:02 -0800, "Jack Yao"
> <JackYao@.discussions.microsoft.com> wrote:
>
>|||Hi Clifford ,
the missing permissions are not in the sysprotects in Master db either. Bear
in mind that these missing permissions are set specificly to the user
assigned to my production database, and these users do not exist in Master
database anyway.
As I said before , I noticed that, everytime I set permission to the
production database, it only goes into syspermissions, so I wrote this scrip
t
to list out permission on my production database, and it works fine.
********************
select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
case actadd
when 1 then 'SELECT ONLY'
when 2 then 'UPDATE ONLY'
when 3 then 'SELECT + UPDATE'
when 4 then 'DRI'
when 5 then 'SELECT + DRI'
when 8 then 'INSERT ONLY'
when 9 then 'SELECT + INSERT'
when 27 then 'SEL+INST+UPDT+DEL'
when 31 then 'SEL+INST+UPDT+DEL+DRI'
when 32 then 'SP EXECUTED'
END [PERMISSION]
from sysobjects
inner join syspermissions
on sysobjects.id = syspermissions.id
inner join sysusers
on sysusers.uid = syspermissions.grantee
and sysusers.name = 'myusername'
order by objects
***********************
So even though I cannot see the green tick in EM interface, I can still see
the setting permissions from that scripts.
Still, there is no way to resolve this issue, as far as I know anyway .. :-(
Jack
"Clifford Dibble" wrote:
[vbcol=seagreen]
> Hi,
> By any chance were these permissions on system objects in master.dbo?
> AFAIK these were never preserved during upgrades.
> Note - In Yukon, we now preserve permissions on system objects during the
> upgrade process. So if you DENY EXECUTE TO PUBLIC on master.dbo.xp_cmdshe
ll,
> we will remember that during the upgrade.
> Regards,
> Clifford Dibble
> Program Manager
> SQL Server Engine
>
> "Sue Hoegemeier" wrote:
>|||HI Jack,
Can you run the following query and report the result?
select id, uid, type from sysobjects where name = 'sysprotects'
select * from syscolumns where id = object_id('dbo.sysprotects')
Thanks
Andrew
SQL Server Engine
"Jack Yao" wrote:
[vbcol=seagreen]
> Hi Clifford ,
> the missing permissions are not in the sysprotects in Master db either. Be
ar
> in mind that these missing permissions are set specificly to the user
> assigned to my production database, and these users do not exist in Master
> database anyway.
> As I said before , I noticed that, everytime I set permission to the
> production database, it only goes into syspermissions, so I wrote this scr
ipt
> to list out permission on my production database, and it works fine.
> ********************
> select sysusers.name [USER_NAME], sysobjects.name [OBJECTS] ,
> case actadd
> when 1 then 'SELECT ONLY'
> when 2 then 'UPDATE ONLY'
> when 3 then 'SELECT + UPDATE'
> when 4 then 'DRI'
> when 5 then 'SELECT + DRI'
> when 8 then 'INSERT ONLY'
> when 9 then 'SELECT + INSERT'
> when 27 then 'SEL+INST+UPDT+DEL'
> when 31 then 'SEL+INST+UPDT+DEL+DRI'
> when 32 then 'SP EXECUTED'
> END [PERMISSION]
> from sysobjects
> inner join syspermissions
> on sysobjects.id = syspermissions.id
> inner join sysusers
> on sysusers.uid = syspermissions.grantee
> and sysusers.name = 'myusername'
> order by objects
> ***********************
> So even though I cannot see the green tick in EM interface, I can still se
e
> the setting permissions from that scripts.
> Still, there is no way to resolve this issue, as far as I know anyway .. :
-(
> Jack
>
> "Clifford Dibble" wrote:
>|||Hi Andrew,
sorry for the mess, it is difficult to show you the results of second query
in proper format since it has too many columns to show in this tiny space
here, but here we go:
the query select id, uid, type from sysobjects where name = 'sysprotects'
return the following results :
id uid type
23 1 S
the query "select * from syscolumns where id = object_id('dbo.sysprotects')"
return:
id 23 56 1 56 4 10 0 1 4 0 0 0 0 0 0 1 N
ULL 2 NULL -1553186121 0 56 7 NULL 1
0 0 0 0 0 NULL NULL
uid 23 52 1 52 2 5 0 2 8 0 0 0 0 0 0 2 N
ULL 6 NULL -1553186121 0 52 6 NULL 5
0 0 0 0 NULL NULL
action 23 48 1 48 1 3 0 3 10 0 0 0 0 0 0
3 NULL 8 NULL -1553186121 0 48 5 NU
LL 3 0 0 0 0 NULL NULL
protecttype 23 48 1 48 1 3 0 4 11 0 0 0
0 0 0 4 NULL 9 NULL -1553186121 0 48
5 NULL 3 0 0 0 0 NULL NULL
columns 23 165 2 165 4000 0 0 5 -1 0 0 0 0 0 0 5 NULL -1 NULL -1553186121 24
37 4 NULL 4000 NULL 0 0 1 NULL NULL
grantor 23 52 1 52 2 5 0 6 12 0 0 0 0 0
0 6 NULL 10 NULL -1553186121 0 52 6
NULL 5 0 0 0 0 NULL NULL
Please let me know how you go with it
Jack
"Andrew Zhu" wrote:
[vbcol=seagreen]
> HI Jack,
> Can you run the following query and report the result?
> select id, uid, type from sysobjects where name = 'sysprotects'
> select * from syscolumns where id = object_id('dbo.sysprotects')
> Thanks
> Andrew
> SQL Server Engine
> "Jack Yao" wrote:
>|||Hi Jack, I have experienced the same issue. On my site it was caused by
rights being granted to any object that has a type other than table,
view or proc. Use the following script to detect.
select b.name, b.type, a.* from syspermissions a, sysobjects b where
a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
order by b.type
To resolve, you must remove the offending syspermissions entries before
upgrading. As soon as SQL2000 the upgrade hits one of the problem
entries it stops populating sysprotects. I cant see any way to resolve
the issue after the 2000 upgrade.
Use the following script to delete the problem entries.
sp_configure 'allow updates', 1
go
reconfigure with override
go
delete from syspermissions where id in (select b.id from syspermissions
a, sysobjects b where a.id = b.id
and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
go
sp_configure 'allow updates', 0
go
reconfigure with override
go
Hope this helps.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
That is exactly what I was talking about , the reason why sysprotects stop
populating the records.
Sadly , my situation is in the limbo since I have already gone through the
upgrade and the records in sysprotects are long gone (so re-upgrade ain't
gonna do any good).
Nevertheless , for the love of SQL :-) , do you habppen to know why SQL
stop populating sysprotects as soon as it hits those offending permissions ?
I mean, what make granting permission to those objects offend SQL at the
beginning with ?
ta
Jack
"Derek" wrote:

> Hi Jack, I have experienced the same issue. On my site it was caused by
> rights being granted to any object that has a type other than table,
> view or proc. Use the following script to detect.
> select b.name, b.type, a.* from syspermissions a, sysobjects b where
> a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr')
> order by b.type
> To resolve, you must remove the offending syspermissions entries before
> upgrading. As soon as SQL2000 the upgrade hits one of the problem
> entries it stops populating sysprotects. I cant see any way to resolve
> the issue after the 2000 upgrade.
> Use the following script to delete the problem entries.
> sp_configure 'allow updates', 1
> go
> reconfigure with override
> go
> delete from syspermissions where id in (select b.id from syspermissions
> a, sysobjects b where a.id = b.id
> and b.type in ('k', ' c', 'd' , 'f', 'k', 'tr'))
> go
> sp_configure 'allow updates', 0
> go
> reconfigure with override
> go
> Hope this helps.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Hi Jack, my guess is that it stops populating sysprotects because SQL
server isn't designed to allow the setting of permissions on objects
like triggers, constraints, primary keys etc. You can't do it through
Enterprise Manager on 7.0 or 2000.
I have traced the problem on our system to a bad script that allocated
all permissions to all objects. During the upgrade, SQL 2000 probably
doesn't know what to do with the offending permission records and just
stops the load of sysprotects, which in turn means no further records
will go in when new permissions are assigned after the upgrade.
If you have already upgraded to 2000, I guess you could fix it by
creating a new database and copying over the objects and data, and then
setting the permissions. That way you have a new sysprotects table.
Cheers
Derek
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Derek ,
not sure what you mean by "create new database and copy over the objects and
data".
Did you mean copy it with the copy database wizard or use the old
fashion "backup/restore" manually ?
Jack
"Derek" wrote:

> Hi Jack, my guess is that it stops populating sysprotects because SQL
> server isn't designed to allow the setting of permissions on objects
> like triggers, constraints, primary keys etc. You can't do it through
> Enterprise Manager on 7.0 or 2000.
> I have traced the problem on our system to a bad script that allocated
> all permissions to all objects. During the upgrade, SQL 2000 probably
> doesn't know what to do with the offending permission records and just
> stops the load of sysprotects, which in turn means no further records
> will go in when new permissions are assigned after the upgrade.
> If you have already upgraded to 2000, I guess you could fix it by
> creating a new database and copying over the objects and data, and then
> setting the permissions. That way you have a new sysprotects table.
> Cheers
> Derek
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>