SQL permissions writing in a view

HansDutting

Hello all,

Im absolutely not an SQL wizard but need some advice on this scenario.

I have this situation:
Database 1 with a table Order (SQL database)
Database 2 with a view to table Order in Database 1 (NAVISION SQL)

The user should be able to read and write in the view of table Order in NAV.
Now this user gets the message that he doesnt have reading permissions to Table Order.
This happens when he activates CSIDE code which modifies the view in NAV.
This user has permissions on database 1: db_datareader and db_datawriter.
He also has permissions in NAVISION to modify, read delete etc..
When we make the user sysadmin of database 1 there is no problem ofcourse.
Question is how to approach and set the correct security settings for this user, without making him db_owner or sysadmin on Database 1?

Who can help me out?
Tnx,
Hans Dutting

bbrown

I take it that both databases are on the same server? In that case then you need to activate the guest account on DB1 and grant it appropriate permissions to the Order table.

jglathe

Hi,

I would guess that it's not possible to write into a view. A view consists of fields from one or several tables, with computed fields, too. Writing is only possible for tables.

with best regards

Jens

bbrown

jglathe wrote:

Hi,

I would guess that it's not possible to write into a view. A view consists of fields from one or several tables, with computed fields, too. Writing is only possible for tables.

with best regards

Jens

There is what's known as "updateable views". These are views that will accept insert,delete,and modify statements. So yes it is possible to write to a view

HansDutting

tnx bbrown,

So what you are saying is that we dont give the windowslogin of the user the permissions but the default "guest" account?
were going to try, I report later on.
Does this mean the user goes indirect to the DB1 instead of directly on his windowsaccount?

gr,
Hans D.

jglathe

Hi,

bbrown wrote:

There is what's known as "updateable views". These are views that will accept insert,delete,and modify statements. So yes it is possible to write to a view

Cool 8) Have to try this.

with best regards

Jens

bbrown

HansDutting wrote:

tnx bbrown,

So what you are saying is that we dont give the windowslogin of the user the permissions but the default "guest" account?
were going to try, I report later on.
Does this mean the user goes indirect to the DB1 instead of directly on his windowsaccount?

gr,
Hans D.

I guess you could think of it like that. A NAV user accesses the NAV database usign the security context of the applicaiton role. When running with an application role, any object permissions assigned directly to the user are ignored. That's why assigning the user permission to the table in DB1 doesn't work.

When a user connects to a database, to which they do not have access, then the permisisons granted to the guest account are used.