How to access sysobjects information through a non admin user

Question

How to access sysobjects information through a non admin user

vijayakumarn

SSChasing Mays

Points: 615
More actions
April 4, 2012 at 7:29 am

#391943

Hi,
How to access metadata information from sysobjects table through a non admin user, but without granting view any definition rights?
Thanks
Vijay

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The answer is grant view definition to that user. Why is that not an option here?

p.s. sysobjects is deprecated, should not be used any longer, included only for backward compatibility with SQL 2000. Use sys.objects.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vijayakumarn SSChasing Mays Points: 615 More actions · Answer 2

Hi,

OK i can also prefer sys.objects, But situation is this, the user should not be given with the grant view any definition permission, but he needs to access the sys.objects information. Is there any workaround for this case?

Thanks for replies

CapnHector SSCoach Points: 16143 More actions · Answer 3

GilaMonster (4/4/2012)
The answer is grant view definition to that user. Why is that not an option here?
p.s. sysobjects is deprecated, should not be used any longer, included only for backward compatibility with SQL 2000. Use sys.objects.

would GRANT SELECT ON sys.objects TO UserBob be the way to go. or does grant select not work on the sys. items

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

capn.hector (4/4/2012)
GilaMonster (4/4/2012)
The answer is grant view definition to that user. Why is that not an option here?
p.s. sysobjects is deprecated, should not be used any longer, included only for backward compatibility with SQL 2000. Use sys.objects.
would GRANT SELECT ON sys.objects TO UserBob be the way to go. or does grant select not work on the sys. items

It's not that he can't query sys.objects, that anyone can do. However it will only show objects that the user has some permission on.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok, what must the user be allowed to do and what must the user not be allowed to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 6

sys.objects is a view which is filtered on the database_principals permissions.

rows of data may exist for an admin which will not be visible to that user; simple enough to prove:

create user [ClarkKent] without login

Execute As USER= 'ClarkKent'

select user_name() --I'm Clark Kent

select * from sys.objects --nothing there! if he can see anything, it's because someone granted to PUBLIC!

REVERT; --change back to superman

drop user [ClarkKent]

the end user has access to sys.objects, but can only see the objects he has access to...not ALL objects that exist.

the only solution is to put the user in a role that grants all the permissions...VIEW DEFINITION is best, as Gail said...a crappy alternative would be db_datareader.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!