Access to application roles

DBA's

We have a situation where an application role has to pull data from more than one database. I know that application role can access data with respect to only that paticular database, but I had a look at the following link:

http://support.microsoft.com/kb/906549

The above states that enabling trace 4616 can solve the problem, but it isn't helping in my case, still when I use the application role I am not able to access the information in other d/b's.

Is there any way to make an application role pull data from other databases (other than using sp_setapprole) each time we wish to view data in other databases.

Thanks
Satya

is your db set to trustworthy ?

No, it isn't, the default property has not been modified

Thanks
Satya

Do you think modifying that property can be a solution for this?

Check the following one...

http://groups.google.com/group/micr...multi database access&rnum=1#eab0579823a32eb9


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Thanks Mohammed, that worked out

But the minimum level of access that has to be given is connect to guest account and then enabling the cross dbownership chaining option worked out.

Thanks
Satya

Strage !

This worked for a moment but later when I try to do the same with just the connect previlages granted to guest account, now it isn't working. Its giving me an error:

select permission denied on 'objname','dbname',schema'dbo'

Thanks
Satya

This works out only when select permission is granted to guest account now. Is there no option other than this?

The 4th and 5th points in the link you have mentioned are rather unclear to me.

Thanks
Satya

Step4 and 5 saying create the views and procedure to access the tables....

Read the followign..

SQL Server 2005 Books Online
Application Roles
http://msdn2.microsoft.com/en-us/library/ms190998.aspx



MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Yes Satya

It is SQL 2005

Yes Satya

1. I have already done that. I have enabled cross db chaining both at d/b level and server level.
2. I made sure the databases have the same owner.
3. The guest account only has connect access to the database and nothing else. I think that's all that we are supposed to do.

It is still not working. The application role isn't able to view data in other d/b's.

Thanks
Satya

Satya & Mohammed

Waiting to hear some expertise from you on this...

I need some time to test... I will get back to you I do my testing on this.


MohammedU.
Moderator
SQL-Server-Performance.com

All postings are provided “AS IS” with no warranties for accuracy.

Ok Mohammed

Take ur time...

What I have tried is enabled d/b chaining and granted only connect access to guest account. It doesn't work.

Only when I give select access, I was able to view the data from the 'AppRole'. So just curious to know if there is any work around other than giving select access to 'guest' account.

Thanks
Satya

No in our environment, after weighing diff options , App roles was concluded to be the best

Ok Satya

Go ahead and do as you wish. I will mail you the req

Thanks
Satya