Cross Database Seccurity RRS feed

  • Question

  • I have a stored procedure that goes across to another database. My understanding is that you eneble cross database chaining and add the user into the database you are crossing to. However my stored procedure is complaining that it does not have select permissions on the table. Any idea how to fix this ?
    Monday, December 14, 2009 11:45 AM


All replies

  • You need to assign the appropriate user rights in the database you are crossing to.

    So, if your stored procedure in Database1 select data from tableA in database2, your user also need SELECT rights on tableA in database2.
    Monday, December 14, 2009 12:02 PM
  • I accept that this would work but is poor from a security perspective.  Throughout my application no one has select permissions but has execute persmissions on stored procedures.  This works fine until I have to go across to another database.
    Monday, December 14, 2009 12:08 PM
  • You can always create view on table and add permission to view.
    Monday, December 14, 2009 12:17 PM
  • Not that easy I am afraid as I need to inner join across the databases.
    Monday, December 14, 2009 12:25 PM
  • Hi,

    The topic was discussed in another thread in the forum, see below:
    Cross database permission: http://social.technet.microsoft.com/Forums/en-US/sqlsecurity/thread/82f8fbfc-327f-4969-9001-b1c50e9599da.

    Hope this helps.
    ChunSong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, December 16, 2009 3:11 AM