Solved

Revoke update on public failed

Posted on 1997-12-09
6
383 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
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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 100 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 38
separate column 24 20
SQL Server 2016 Developers Edition Install 4 57
Sql query for filter 12 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now