Cross database permissions

  • I have a situation where tables from 3 current databases are to form part of a 4th database. In order to prevent rewriting the current applications I thought of creating views as virtual tables in the old databases so that the applications would continue to think they were updating the local tables, but were in fact updating the new 4th database.

    However I have been nobbled by the inability to update the 4th database because it requires explicit update access to the tables, rather than the implicit access awarded by having exec authority on the procedures.

    How do I get round this without:

    1). Granting update on tables in 4th database

    2). Rewriting current applications to call procedures in 4th database?

  • I'm not sure what you are referring to. It seems obvious to me that, if the app is updating tables directly, you will need update rights on that table (either in the same database or in another one).

    I think you might get away by using a 'instead of' trigger on the tables in the 'old' databases. You could write these to use the SP's in the 'new' database, since you have to write them anyway.

    Effort depends on the number of tables in the current databases.

  • No, if the app is using stored procedures, then you do NOT need explicit rights to the tables it references - that is the whole point of the security model. It works if the tables are in the same database as the procedures, but not if they are in a different database.

  • I believe the ownership chain will cross the databases if the table owners (and proc owners) are the same. This either works pre SP3 or post SP3, but not both, so beware.

    I think you should create a role for this and grant update only permissions in db4. I like the idea of using the procs in dbs 1,2,3. Gives you flexibility if you decide to merge dbs 4 with another. Less changes.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • You're completely correct. With stored procedures, you don't need permissions on the tables it references.

    I thaught that your app was referencing your tables directly, but apparently I was mistaking.

    Am I correct when I assume that your current app use stored procedures to access the tables. But when moving the tables outside of the current database, you have to give rights to 'the' user on the external tables?

    In that case, the instead of trigger still might limit your effort, if the number of SPs is a lot higher than the number of tables they reference. Meanwhile, I'll be thinking of an alternative solution...

  • Yes, Steve, I also believed the ownership chain would cross databases, all my objects are owned by dbo! It doesn't work though. I am on SP2. Reading the documentation for SP3 it indicates that cross database permissions will not be the default in future and will be database selectable. Big problem here is that it indicates that they are active by default in SP2 and before - so it should work!!!

    NPeeters instead of trigger solution is beginning to look like the answer, but I shouldn't have to do this >:o(

    The really REALLY stupid thing (I'm getting wound up here, aren't I?) is that triggers work. A user doesn't have to have any rights in an audit database except access to that database to write audit records via the trigger. How is that different from running a stored procedure to do the same thing?

    Microsoft have screwed up again and as usual I have to write a couple of thousand lines of code to get round their inadequacies.

    Edited by - millennia on 12/02/2002 10:32:59 AM

  • Solved the mystery (I think)...

    I have also tested the ownership chains in SQL2000 SP2 with dbo as owner. Turns out that apparently the dbo account is NOT the same user in both databases. The SID in sysusers is different.

    So I tried it with a user I created myself, and it works like a charm. So I guess there is something fishy going on with the dbo owner...

  • SP3 may solve this issue for you when it's released. Since it's still in beta, I don't think we're allowed to speak as to why until it goes public. Unless, of course, it was covered at PASS?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • Lot was coverend. Don't think you're under NDA, but I could be wrong.

    Thanks for the update, I should have specified that the owners need to be the same SID.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • As far as I know we are not. I will double check to be 100% sure, but I don't think that the database owner is an issue. I beleive the default dbo is the actuall creator of the DB. I will check this tomorrow and wqith SP3.

  • I know with SQL Server Notification Services and with Win2K SP3 we weren't supposed to discuss anything. I was thinking the same rules apply for the SQL Server service packs.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • You're all stars (particularly NPeeters in this case as he put the finger on the problem). I checked the SIDs of the dbo user in each database, and on the target database the SID was for a login that didn't even exist in the sysxlogins table!!! All other dbo users in databases were the sa login.

    I therefore updated the sysusers table in my test target database to a sid of 0x01 (sa) and it now works!!!

    How this one database happened to get in this state I don't know (could due to the original databases being creaed in SQL Server 6.5 - and therefore by sa - while the new database was created originally in SQL 7.0 and probably by a DBA that has since left and had his userid deleted!)

    Anyway I can now press ahead with my 4th database knowing it will work.

    By the way - I am beta testing SP3 so I know all about the new features. I tried it on SP3 and it didn't work - which makes sense as the SIDs were wrong on that server also. After amending the SIDs the new functionality works as expected.

    Cheers!

  • Definently in the standard agreement we are not supposed.

  • Another possiblity is using "instead of" triggers to direct all updates/inserts/deletes to the new data base. Your existing app continues to work unchanged. The maintenance shifts to the triggers until you can get the app coders to update their procs.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply