[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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
0
used2could
Asked:
used2could
  • 4
  • 2
1 Solution
 
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
 
chapmandewCommented:
make sure that the use you denied doesn't have admin permissions.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
chapmandewCommented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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