Disable selects on sysobjects table for user

Is there any way we can disable a ms sql user from doing selects (or any interaction) on the sysobjects table?

Thanks
~B
used2couldAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
OK, just did some research.  If the user is a member of the db_owner role, but has been denied view permissions on sys.objects, then they cannot see the system view.  The reason is because this view resides in the mssqlsystemresource database.  Here is the error I got when trying to view the data:

The SELECT permission was denied on the object 'objects', database 'mssqlsystemresource', schema 'sys'.

But, as db_owner, I can do anything else I want in the db.
0
 
chapmandewCommented:
hi, try this:

use master
go
deny select on sysobjects to sqlusername
0
 
used2couldAuthor Commented:
Chapmandew,
I've tried that but after running it i was still able to do a select on the sysobjects table with the user i am trying to block. Have any idea why i would still be able to? the sql executed successfully

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
chapmandewCommented:
make sure that the use you denied doesn't have admin permissions.
0
 
Scott PletcherSenior DBACommented:
Be sure to run the command in the db you want to deny permissions on, not necessarily the master db.

USE relatedDbName

DENY SELECT ON sysobjects TO username

If the user is 'dbo' / 'db_owner', I'm not sure DENY has any effect.
0
 
chapmandewCommented:
I am pretty sure it doesn't have an effect if they're the owner or an sa.
0
 
Scott PletcherSenior DBACommented:
I know it doesn't have any effect if they're 'sa'.
0
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.

All Courses

From novice to tech pro — start learning today.