Permissions on views

I like to give a user permission to crete views but don't want him to change/modify/delete data or table. What is the best way to setup this user?

Thanks.

Jammy
jlisloAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
The "GRANT CREATE VIEW TO user_name" just gives that one specific permission.  You can go into Query Analyzer and issue that command, and that user can then create views (and alter/delete any views he/she created, but no other users' views).

Db_datawriter, etc., are roles that essentially grant a lot of permissions.  I don't think that will help you here, because the only role (other than db_owner) that includes CREATE VIEW is db_ddladmin, and that gives a lot of other rights as well (such as ALTER TABLE, CREATE INDEX, CREATE TABLE, etc.).
0
 
Scott PletcherSenior DBACommented:
You can issue this command:

GRANT CREATE VIEW TO user_name
0
 
jlisloAuthor Commented:
It this work for only one view or is a long time grant until I DisGrant (I don't know the command) one month later?

Thanks.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Scott PletcherSenior DBACommented:
That command will allow that user to create any number of views.  It does stay in effect until you issue a REVOKE, so it would be in effect one month later, one year later, etc..  
0
 
Scott PletcherSenior DBACommented:
Also, that user will be able to DROP any view he/she created, but not a view created by another id.
0
 
jlisloAuthor Commented:
Can I give the use just Public permision or I have to give him something like db_datawriter...etc rights.

I know I ask a lot so I increase points.

Thanks.
0
 
jlisloAuthor Commented:
Thank you very much for the help.
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.