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?
 
Scott PletcherSenior DBACommented:
You should be able to GRANT them "VIEW DEFINITION" privileges on the views you want them to see.
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
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.