| Author |
Topic |
|
rdl
Starting Member
25 Posts |
 Posted - 2011-05-21 : 22:14:20
|
| Hi all,I have this view and would like to grant permissions - select, insert, update, delete on it to user 'client':CREATE VIEW PendingPersonReport ASSELECT A.ReportRef, B.IDfrom report A, reportperson BWHEREA.ReportStatus = 'Pending'ANDA.ReportRef = B.Report.RefGROUP BY A.ReportRefThis view selects the report reference and id of person where report status is pending.Can anyone help for the permissions please to user 'client'.Thankyou :) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rdl
Starting Member
25 Posts |
Posted - 2011-05-22 : 09:47:03
|
| Hi,I would like to ask a question. does ALL includes SELECT, INSERT, UPDATE, ALERT, DELETE in it??As i want only the select, insert, update and delete right?!Thanks,Rose |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-05-22 : 10:06:47
|
if you are using SQL 2005 or later, you should specify the permissions required specifically. using ALL will give you a deprecation warning message.GRANT SELECT, INSERT, UPDATE, DELETE ON PendingPersonReport to client refer to http://msdn.microsoft.com/en-us/library/ms188371.aspx
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
rdl
Starting Member
25 Posts |
Posted - 2011-05-22 : 10:50:42
|
| Thanks :) |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-22 : 12:24:52
|
The view you have defined is not updatable. That means you will not be able to grant INSERT, DELETE or UPDATE permissions on this view.There are several rules which apply to updatable views which include the following from Books Online:quote:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause
Jeff |
|
|
|
rdl
Starting Member
25 Posts |
Posted - 2011-05-22 : 18:30:36
|
| Yeh, infact I tried to insert and couldn't .....when having foreign keys you can't update??so is it possible to have a view on 2 tables??? or is it just a view and not insert/update etc??? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-05-22 : 18:41:33
|
it is because you have a GROUP BY in your view. Read the quote that Jeff posted.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-05-22 : 20:33:05
|
LOL 
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
Grant permissions on view