Access to application roles | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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.

Are you using SQL 2005> Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Yes Satya It is SQL 2005
For cross DB chaining to work, the databases must also have the same owner. To enable the guest user in the non-application role databases so that users have a security context after the application role is enabled. However, no permissions need to be
granted to guest or public and you can control this by setting up necessary access privilge on that view or sp.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
For cross DB chaining to work, the databases must also have the same owner. To enable the guest user in the non-application role databases so that users have a security context after the application role is enabled. However, no permissions need to be
granted to guest or public and you can control this by setting up necessary access privilge on that view or sp.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
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
Is there any way can you achieve without using the application roles here?> Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
No in our environment, after weighing diff options , App roles was concluded to be the best
Can we this discussion offline and post the results there after, if you wish to. (mail me your requirement). Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Ok Satya Go ahead and do as you wish. I will mail you the req Thanks
Satya
]]>