Revoke update on public failed

I tried to revoke the update right on public for Tb1 and grant userA this right using both the enterprise manager and isql "revoke update on Tb1 from public' and received no error msg. But when i use the enterprise manager or the sp_helprotect to view Tb1, the right is available to all users and public.  Why is it so?
myschanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

myschanAuthor Commented:
Edited text of question
0
JohnnyBoyCommented:
Are you looking at the table or the rights to the table ?
0
myschanAuthor Commented:
I think I am looking at the rights to the table:  The 'sp_helprotect Tb1' actually returned a list of 39 rows of Select/updat/... rights to each users including public.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cymbolicCommented:
Have you run DBCC CheckDB lately?
0
TMSCommented:
Seen that once before. There was a duplicated record in sysprotects. Revoke or enterprise manager did no help. Don't know how it happened...

We had  to allow direct updates against system tables and then manually delete the faulty row.
This is tricky because system tables are well normalized and you have to deal with 'ids' coming from several different tables... (need many joins or a good memory :)
Hope this helps.
0
Jim_SQLCommented:
Hi,

Try running this code, it does generate some errors (but they don't matter). This only applies if the table in question was created by another user other than 'sa'. You will have to do a search and replace of XXX with a public user on your system, and you will have to be logged in as 'sa'.

drop table PerTest
drop table XXX.PerTest
GO
PRINT "CREATING TABLE AS 'sa'"
create table PerTest (Q varchar(255))
GO
sp_helprotect 'PerTest'
GO
PRINT "GRANTED ALL TO PUBLIC AS 'sa'"
grant all on PerTest to public
GO
sp_helprotect 'PerTest'
GO
PRINT "REVOKED UPDATE TO PUBLIC AS 'sa'"
revoke update on PerTest to public
GO
sp_helprotect 'PerTest'
GO
PRINT "DROPPED 'sa' VERSION OF TABLE"
drop table PerTest
GO
PRINT "SWITCHED TO A PUBLIC USER"
setuser 'XXX' -- 'SomeOneInPublicGroup'
GO
PRINT "CREATING TABLE AS 'public user'"
create table PerTest (Q varchar(255))
GO
sp_helprotect 'PerTest'
GO
PRINT "GRANTED ALL TO PUBLIC AS 'public user'"
grant all on PerTest to public
GO
sp_helprotect 'PerTest'
GO
PRINT "REVOKED UPDATE TO PUBLIC AS 'public user'"
revoke update on PerTest to public
GO
sp_helprotect 'PerTest'
GO
-- BUT !!!!!!!!
PRINT "GRANTED ALL TO PUBLIC AS 'public user'"
grant all on PerTest to public
PRINT "---------------------- THE BIG BUT ----------------------"
GO
sp_helprotect 'PerTest'
GO
PRINT "SWITCHED TO 'sa'"
setuser -- Puts you back as SA
GO
PRINT "REVOKED UPDATE TO PUBLIC AS 'sa'"
revoke update on XXX.PerTest to public
GO
sp_helprotect 'PerTest'
GO
PRINT "TRIED TO REVOKE UPDATE TO PUBLIC AS 'sa' AGAIN"
GO
revoke update on PerTest to public
GO
sp_helprotect 'PerTest'
GO

Lesson to be learned... SA doesn't get to change
the permissions on other people's tables... (I
think this is crazy!)

(Hope this helped.)
Jim

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.