sys tables grant access to sql user

Question

sys tables grant access to sql user

HildaJ

SSCarpal Tunnel

Points: 4516
More actions
July 7, 2014 at 7:47 am

#288357

Good morning all. I have a SSRS report that shows possible unnecessary indexes that I'd like to publish so we can run it as needed. The problem seem to be that the SQL Account I created does not have access to the sys tables (in this case for this report sys.tables, sys.schema, sys.indexes and [dm_db_index_usage_stats]). The SQL account is part of the database public group but it cannot see the data from the sys tables. How do I grant access to this user so it can select from the sys tables?
I tried the following an it doesn't seem to work.
GRANT SELECT ON sys.table TO User1
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

to grant a user the ability to see the metadata stuff for any object in a database, without granting access to the data within those objects, you can grant VIEW DEFINITION to a specific database user or role:

USE SpecificDatabase;

GO

GRANT VIEW DEFINITION TO HildaJ

additionally, there is a server wide equivalent you can grant to a login, which grants the ability to see the metadata in all databases, without the login being a user in each database:

GRANT VIEW ANY DEFINITION TO Lowell;

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!

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 2

Lowell,

Thank you so much for the response and very quick response. I tried this and it work but I had to add the user to the public group for the database in question though. Otherwise, it would throw an error when I tried to run the SSRS report. However, after adding the user to the public group it worked like a champ.

Thank you.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

I would avoid using the public group. Try the following instead:

use your_target_database

go

create role db_view_schema authorization [dbo]

grant view definition on schema::[dbo] to [db_view_schema]

go

exec sp_addrolemember @rolename='db_view_schema', membername='security_account'

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."