Post reply

Help.. DB Lost Owner

  • July 23, 2007 at 11:15 am

    #62608

    Hi,

     

    I found some of my production database lost it’s owner. It’s NULL now. Could you please help me finding below information:

    1. How to find who was the previous owner?

    2. What may the cause of this issue?

     

    Many thanks in advance.

     

    Regards

    Utsab Chattopadhyay


    CDBA

  • July 23, 2007 at 12:37 pm

    #721618

    1. Sorry that may not be possible

    2. If you removed the login that was the owner of that DB you are causing this issue.

    That is why is usually a good practice to make sa the DB owner (can't remove sa login )


    * Noel

  • July 23, 2007 at 1:14 pm

    #721636

    Hi Noel

    Thanks for the tips.. But we do not removed any login from the server now..So I am totally confused how this could happen

    Any though please?

    Regards

    Utsab Chattopadhyay


    CDBA

  • July 23, 2007 at 1:30 pm

    #721645

    As the previous poster says, the login which owned that database has been dropped.  You can readd the login back to your SQL Server or reassign the database ownership to a new login.

    In order to reassign ownership you can run sp_changedbowner from the context of the database in question.

    For example:

    USE <database name>

    GO

    EXEC sp_changedbowner 'sa'

    You must be a member of sysadmin or the current db owner, to make the change.  If the new owner is a current user of the database, you must drop the user first.  Aliases are remapped to the new owner.

    Regards,

    CVM

     

  • July 23, 2007 at 1:31 pm

    #721647

    The other posiblility is that you restored a database comming from a different server and the login that owned the DB on the source server does not exists or is not remapped in the new server.


    * Noel

  • July 23, 2007 at 1:50 pm

    #721661

    Thanks Again Noel/CVM. But I did no change on the server. Something happened by itself. Can it be for some serious security issue?

    I am facing it first time


    CDBA

  • July 23, 2007 at 2:47 pm

    #721691

    >> Something happened by itself.

    That's extremely unlikely.

    Who was the database owner before this happened?

     

  • July 23, 2007 at 3:40 pm

    #721711

    1. the owner's login got dropped/deleted

    2. the owner had their permission revoked. If the database box got unchecked for their login or the dbowner box was unchecked, this will happen.

    3. the security on the server changed. If SQL Server was using Mixed Authentication and the owner used a SQL Server account and then the authentication was changed to Windows Only, I believe this would happen.

    4. If the owner belonged to the same domain and then one of the domains changed (Server and Owner on Domain A, then owner changes to untrusted Domain B).

    So something DID change, you just have to figure out what and who made the change.

    -SQLBill

  • July 23, 2007 at 11:11 pm

    #721754

    Hi

    The owner was sa.. This is the part what I cann't understand. I went through the log and found nothing which could interest me on it

    Could any one kindly give me any idea on how can I have an investigation on it?

    Thanks in advance

    Regards

    Utsab Chattopadhyay


    CDBA

  • July 25, 2007 at 5:33 am

    #722216

    This can be caused by Active Directory changes under the following sequence of events

    1. In AD, create a group named DBAs

    2. In AD, add account "domain\Carl.Federl" as a member of the DBA Group

    3. In SQL Server, grant login to domain\DBAs and grant sysadmin rights.

    4. In SQL Server, "domain\Carl.Federl" connects and creates a database - "domain\Carl.Federl" is now the database owner.

    5. In AD, remove the account domain\Carl.Federl

    The owner of the database is now unknown!

    This will also occur when the AD account was a member of the local administrators group, BUILTIN\Administrator has the sysadmin role and then the AD account is removed.

    This can also occur if there were multiple AD domains, the domains were merged and then a domain was removed.

    SQL = Scarcely Qualifies as a Language

  • July 26, 2007 at 8:48 am

    #722596

    Perhaps someone did this:

    update master.dbo.sysdatabases set sid = NULL where dbid = db_id()

    That would do it.

Viewing 11 posts - 1 through 10 (of 10 total)

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