granting permission to a view doesn't work

[kursula]

Hi,

I have a SQL 2005 database with users that have permission through a role to update, select, delete and insert in my tables. i have given permission also to a "view" (query) containing only fields from tables that they have permission to update, select, delete and insert into. But when i test it , they can't 'view the view' - the error message is 'can't find object myview'.

anybody tried this and knows the solution?

 

[mdlueck]

Please show us the exact SQL you enter which is coming up with that error message.

 

[SQL_Hell]

I am guessing the view is in a different Schema

 

[kursula]

Hi mdlueck and SQLhell !

thnx, i realised now that the problem isn't in SQL, since the user can open the view fine directly, the problem is in the access UI i have made, here is the code i use:
-----------------------------------------------------
Private Sub cmd_VisPoster_Click()
On Error GoTo Err_cmd_VisPoster_Click

Dim stDocName As String

stDocName = "VisPoster"
DoCmd.OpenView stDocName, acViewNormal, acEdit

Exit_cmd_VisPoster_Click:
Exit Sub

Err_cmd_VisPoster_Click:
MsgBox Err.Description
Resume Exit_cmd_VisPoster_Click

End Sub
--------------------------------------------------------
PLEASE NOTE: when i use my sa login the above code works fine!

 

[kursula]

bytheway it's Access 2007

 

[SQL_Hell]

So the user can see the view in Mangement studio?

What schema is the view in? default will be dbo

Maybe give the user db_owner permissions to test whether it is some permissions issue.

 

[kursula]

So the user can see the view in Mangement studio?
exactly
What schema is the view in? default will be dbo
yes it's dbo
Maybe give the user db_owner permissions to test whether it is some permissions issue.

yes they do actually have db_owner permissions (i have a limited (18) and trusted user group

anyway, just found the solution in another forum, all that was needed is the add dbo to the name string, so that the code is now

-----------------------------------------------------
Private Sub cmd_VisPoster_Click()
On Error GoTo Err_cmd_VisPoster_Click

Dim stDocName As String

stDocName = "dbo.VisPoster"
DoCmd.OpenView stDocName, acViewNormal, acEdit

Exit_cmd_VisPoster_Click:
Exit Sub

Err_cmd_VisPoster_Click:
MsgBox Err.Description
Resume Exit_cmd_VisPoster_Click

End Sub
--------------------------------------------------------

thanks
have a good one !

 

[SQL_Hell]

Cool, I thought it might be that, or in a different schema. Hence asking what the schema was