Solved

Revoke update on public failed

Posted on 1997-12-09
6
385 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

914 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

16 Experts available now in Live!

Get 1:1 Help Now