SQL Server Performance

Access to application roles

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, May 1, 2007.

  1. satya.sqldba New Member

    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
  2. alzdba Member

    is your db set to trustworthy ?
  3. satya.sqldba New Member

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

    Thanks
    Satya
  4. satya.sqldba New Member

    Do you think modifying that property can be a solution for this?
  5. MohammedU New Member

  6. satya.sqldba New Member

    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
  7. satya.sqldba New Member

    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
  8. satya.sqldba New Member

    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
  9. MohammedU New Member

    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.
  10. satya Moderator

    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.
  11. satya.sqldba New Member

    Yes Satya

    It is SQL 2005
  12. satya Moderator

    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.
  13. satya Moderator

    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.
  14. satya.sqldba New Member

    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
  15. satya.sqldba New Member

    Satya & Mohammed

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

  16. MohammedU New Member

    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.
  17. satya.sqldba New Member

    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
  18. satya Moderator

    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.
  19. satya.sqldba New Member

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

    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.
  21. satya.sqldba New Member

    Ok Satya

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

    Thanks
    Satya

Share This Page