Database owner

  • I have lots of databases that were created by several people - so they ended up being 'owned' by each of the creators. I would like (if possible) to have them all "owned" by the local admin account - not an individual. What should I look out for before I change the owner of these databases? I don't want anything to stop working in the name of conformity. Thanks!!

  • What you're really talking about is changing what schema the tables belong to.

    What you have to watch out for is any query that uses > 1 part naming convention:

    <server>.<database>.<schema>.<table>

    So, if the table belongs to schema xyz, and you reassign it to the dbo schema, then any query that references xyz.table specifically will fail.

    If, like many queries, there isn't a schema qualifier, then when that user tries to run the schema, sql will determine that the table doesn't exist in that schema, and will go up the schema-chain and will find it in the dbo schema.

    i.e.:

    select * from xyz.table will fail, while

    select * from table will not fail

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • shairal_t (6/2/2010)


    I have lots of databases that were created by several people - so they ended up being 'owned' by each of the creators. I would like (if possible) to have them all "owned" by the local admin account - not an individual. What should I look out for before I change the owner of these databases? I don't want anything to stop working in the name of conformity. Thanks!!

    As you mentioned,

    I assume that you want to change the "owner" of database, not the db_owner role, don't you?

    As far as i know, sql server have two "owner", one is the "owner" which is created when you create database and can not change, another one is the "db_owner" role which is assigned to user of sql server.

    So, if i'am right, i don't know how you can change the "owner" of database?

    Regards,

    Sol

  • U CHANGE THE OWNER BY

    use master

    GO

    EXEC [DB_NAME].dbo.sp_changedbowner @loginame = N'LOGIN_NEW', @map = false

    GO

  • Yes - I would like to change the owner by using sp_changedbowner not change the schema - sorry for the confusion. So my question would be, what problems would I run into if I make them all the same? If they were all created by admins, does it even matter if they're the same?

  • 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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