Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Restored SQL Server Database, but am not the owner.

    Hello,

    I had to format my computer and I backed up my SQL Server 2005 database to my external hard disk. I re-installed Windows Vista and SQL Server 2005, Visual Studio 2008 as well.

    When I came to restore the database which I backed up, SQL Server says Im not the owner. So I cannot access it properly. To access it (in vs2008 also) I have to run as Administrator, but I still dont have full priveledges. If I right-click the database and choose properties/files I canot add an owner either.

    How do I make myself the owner of the database?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think the GUI allows you to make this change.

    Try the sp_changedbowner stored procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    I tried this when I ran sql server as administrator

    EXEC sp_dbcmptlevel 'dbname', '90';
    ALTER AUTHORIZATION ON DATABASE::dbname TO sa

    this allowed me to install the db diagram as administraor, but not when running as a user.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Allowed you to install the db diagram?
    OK, now I am confused about exactly what you are trying to do.
    Please, please, please, give careful detailed explanations in your posts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Exactly as I have said in my two posts, I have restored my database in SQL SERVER 2005 Studio Express, but It says I am not the owner. So I cannot even connect to it in VS2008 (for the front end I am building) unless I run VS2008 as administrator. If I run SQL Server as administrator it will allow me to create a db diagram, but if I run as a user I cannot, it says - "A member of the db_owner role must use the database diagramming functionality in order to setup the required database diagramming objects on the SQL Server".

    So there is clearly an ownership issue with the database.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So you can log into you database as an administrator successfully, and you have full priveleges. But you cannot log in as a user?

    Have you set up a server login for the user, and has the user been granted rights to access the database?

    And why are you trying to create a database diagram as a user, anyway?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    One more thing if you are able to restore the DB then only you can access it from the front end.
    First of all try to restore the DB as administrator and give the relevant permissions to the required user and then try connecting thru the front end.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pavan_kashyap
    One more thing if you are able to restore the DB then only you can access it from the front end.
    Huh? An admin can access any database. You should not need to reinstall the database in order to change ownerships or permissions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •