Restore (database onwer)

  • Hi,

    When i restore a tabase by using:

    restore database db_name from disk ='c:\test.bak' , after the restore the owner of the database becomes the Login with witch we made the restore.

    What do you guys do? do you let the owner be the login that restored the database, or do you use other method?

    Thank you

  • Since we like our databases to be owned by sa, I just run sp_changedbowner 'sa' after the restore.

    Greg

  • Some third party apps require a particular ID be the dbo, so watch out for that.

    Having the db owned by an ID that restored it is not in itself a problem, worse that can happen is if the person leaves the company say and their ID is removed from AD (presuming windows authentication) then the owner becomes 'unknown'. the database will continue to function, sp_helpdb fails though, which can be a PITA.

    Usual standard is to make all dbs have the same owner, usually SA. There is a security risk in this though, IIRC to do with cross database chaining, our resident security expert Brian Kelley is not a fan.

    ---------------------------------------------------------------------

  • george sibbald (5/14/2010)


    Some third party apps require a particular ID be the dbo, so watch out for that.

    Having the db owned by an ID that restored it is not in itself a problem, worse that can happen is if the person leaves the company say and their ID is removed from AD (presuming windows authentication) then the owner becomes 'unknown'. the database will continue to function, sp_helpdb fails though, which can be a PITA.

    Usual standard is to make all dbs have the same owner, usually SA. There is a security risk in this though, IIRC to do with cross database chaining, our resident security expert Brian Kelley is not a fan.

    What security risk is their with using SA as the owner? I am not aware of any security issues - unless you have users accessing the system using the SA account (why?).

    Not sure how cross database ownership chaining would be affected by the owner of the database.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • thanks Jeff, now I have to try and find the thread and I can't even remember what the original subject matter was! :ermm:

    It was something around it being easier to call objects in other databases as the owner would be the same. I used IIRC for a reason.

    I'll try and find the thread. Maybe I'll ping Brian?

    ---------------------------------------------------------------------

  • This isn't the thread I was thinking of but Brian raises the same point:

    http://www.sqlservercentral.com/Forums/Topic418501-149-1.aspx

    so, should cross database chaining be enabled, people could access objects in other databases without permission checks. People might enable it and not realise this.

    I think it is a a small risk myself but worth noting. Do with it what you will.:cool:

    edit - and its not so much the owner is SA but the owner of all databases is the same.

    ---------------------------------------------------------------------

  • George,

    I can see how that might be an issue - if that is option is enabled. However, if you have enabled that option - then you obviously need to be able to access objects in other databases without having to add users to all databases.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • What I take from this is if all your databases have the same owner,and a requirement for database chaining comes up, enable it at the database level, not the server level.

    ---------------------------------------------------------------------

  • george sibbald (5/14/2010)


    What I take from this is if all your databases have the same owner,and a requirement for database chaining comes up, enable it at the database level, not the server level.

    Agreed - although there isn't a chaining option at the database level. You would set that up by granting access to the necessary users - or, create a user with no login - the appropriate certificate and access and then use execute as for those procedures that need access in the other database. Or, other options...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • ?

    http://msdn.microsoft.com/en-us/library/ms188694(SQL.90).aspx

    ---------------------------------------------------------------------

  • Interesting, didn't know that - but then again I don't set that option or use it. Besides, did you see the comment that if you set it that you should set it on all databases?

    Setting cross db ownership chaining to 1 is not recommended unless all of the databases hosted by the instance of SQL Server must participate in cross-database ownership chaining and you are aware of the security implications of this setting. For more information, see Ownership Chains.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • oh yes I was aware of that, but just because you should not do something does not mean someone won't do it. 🙂

    your faith in humanity is refreshing!

    ---------------------------------------------------------------------

  • honestly i cannot any of this would generally be an issue. I do not enable CDOC, as the KB says you must be fully aware of the security implications. If required i would segregate this db and any others to a separate instance.

    I always set database owners to a valid SQL account and then disable it, rather than using a Windows account!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • george sibbald (5/14/2010)


    I think it is a a small risk myself but worth noting. Do with it what you will.:cool:

    A quote from one of my earlier posts. So I agree with you. Its all for the benefit of people who don't read KB articles.

    ---------------------------------------------------------------------

  • Ok, i will put all the databases with the same owner "SA".

    But i did not understood one thing....

    Why do you say that all your databases have a owner that is a SQL Server account and that that account is disabled?

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

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