Post reply

Database owner

  • June 2, 2010 at 4:25 pm

    #221697

    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!!

  • June 2, 2010 at 6:45 pm

    #1175639

    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

  • June 2, 2010 at 8:55 pm

    #1175665

    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

  • June 3, 2010 at 12:17 am

    #1175707

    U CHANGE THE OWNER BY

    use master

    GO

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

    GO

  • June 4, 2010 at 8:49 am

    #1176574

    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?

  • June 7, 2010 at 1:39 am

    #1176952

    🙂

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

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