Creating a new Role for SQL Server with View Details

I have a group of analysts who currently have been assigned the role "datareader" to a database in SQL Server. These analysts need to view the "details" behind a "view". Currently, they can only execute the view and see the results. Does anyone know how to give a group or assign permissions to allow the details of view? If you right click on a view and select "create as" will allow show the details but allows the user to "create" or alter a new or existing view.

Any help would be appreciated.
Stacey FontenotAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
f you right click on a view and select "create as" will allow show the details but allows the user to "create" or alter a new or existing view.
If they have only datareader role then they can't create objects. If they can create objects, then it means they have more than datareader role.
0
Stacey FontenotAuthor Commented:
I agree. They currently cannot create objects. I as an admin can right click and create objects. I was explaining what information I wish for the them to view. So maybe create a custom role?
0
Scott PletcherSenior DBACommented:
You should be able to GRANT them "VIEW DEFINITION" privileges on the views you want them to see.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use the following command:
GRANT VIEW DEFINITION ON ViewName TO userName

Open in new window

You'll need to repeat that for each View and each user. You can always reduce the number of GRANT commands if you create a role and add the objects and users to that role. Then you can run a single command:
GRANT VIEW DEFINITION ON RoleName

Open in new window

0
Stacey FontenotAuthor Commented:
If I have 100 views, do I have to grant definition for each or can I grant for all views?
0
Scott PletcherSenior DBACommented:
You'd have to grant it to every view.  You can easily generate the code to do that:

SELECT 'GRANT VIEW DEFINITION ON [' + name + '] TO [<user_name>];'
FROM sys.views
ORDER BY name

Then run the code that gets returned.

There is a system-level command of:

GRANT VIEW ANY DEFINITION TO [<login_name>];

But that has sometimes led to (clever) users working their way up to more permissions.  So you have a choice of which way to give users access to the view defs: safer but more work, less safe but easy.
0
Stacey FontenotAuthor Commented:
The users are simple analyst who just need to review the definitions to assist. Thanks.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.