Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

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?
0
myschan
Asked:
myschan
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now