?
Solved

Revoke update on public failed

Posted on 1997-12-09
6
Medium Priority
?
401 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:myschan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Author Comment

by:myschan
ID: 1089568
Edited text of question
0
 

Expert Comment

by:JohnnyBoy
ID: 1089569
Are you looking at the table or the rights to the table ?
0
 

Author Comment

by:myschan
ID: 1089570
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
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 9

Expert Comment

by:cymbolic
ID: 1089571
Have you run DBCC CheckDB lately?
0
 

Expert Comment

by:TMS
ID: 1089572
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
 
LVL 2

Accepted Solution

by:
Jim_SQL earned 200 total points
ID: 1089573
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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question