Change DB and Object owner

  • Hi,

    I created a database under SA account (objects also owned by SA)

    Now I want to change the database and object owner to windows user, what are the procedures should I follow.

    I have windows user created with no permissions in SQL Server.

    Thanks in advance.

    RS.

     

  • Look at sp_changedbowner in BOL.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Open Enterprise Manager. Expand to Security. Find the login, double click on it. Give it permissions to the database including db_owner.

    Open Query Analyzer: Run:

    USE dbname

    exec sp_changedbowner 'new_owner_login'

    For example:

    USE MyDatabase

    EXEC sp_changedbowner 'SQLBill'

    -SQLBill

  • Thnaks John, I did and able to change the DB owner but having problem with object owner change.

  • Hi SQLBill,

    Its SQL Server 2005. I did that but not able to change the object owner.

    Should I add that user in sysadmin role before change the DB owner, would you please tell me the procedure to do this?

     

  • This is what I exactly did.

    Loged into management studio using SA

    created database test

    created a table Table_1 in test database. When I look for owner (both object and database ) which is SA.

    I created windows user (SQLAdmin)

    Created login for MachineName\SQLAdmin

    EXEC

    sp_changedbowner 'MachineName\SQLAdmin'

    sp_changeobjectowner

    'Table_1','MachineName\SQLAdmin'

    I got the error

    Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 107

    Database principal or schema 'SQLAdmin' does not exist in this database.

    Would you please help me on this.

     

  • Hi,

    First I have two questions : why are you using the sp_changedbowner instead of management studio ?

    Why do you want this specific user became the owner of a table instead of the DB ?

    Now for the error message, I do not think that a user can be the owner of only one object (a table here) in your db.

    I am not really an expert in MSSQL 2K5 , but I think (as well as in SQL2K) dbo means database owner.

     

  • first u should create user in ur test database on login SQLAdmin

    second : give permission to created user or addrolemember to db_owner.

    that's all.....

  • Hi, Thanks for your response. I tried from management studio didn't find a way to change.

    alkrup, I created login and created user in test db with db_owner option. When I run sp_changeDBOwner getting the following error

    Msg 15110, Level 16, State 1, Line 1

    The proposed new database owner is already a user or aliased in the database.

    Again all thanks for your help.

  • Delete that user from the database, then you should be able to change it with no problem.  (When you change owner and go back under users for that database, you'll see your Windows user as dbo.)  The same thing happened with me on some software that required a particular user as the owner of the objects.

  • Thanks Shane! Would you please explain what you did?

     

  • Try this:

    ALTER SCHEMA NewOwner TRANSFER ;

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • Occasionally, particularly under SQL Management Studio Express when you create a user as a dbowner of a database, the schema is created but not linked.

    In Studio manager, drill into the database and security, users, and the properties of your user. In the

    Schema's Owned by this user - ensure the schema for your user is ticked on, this will add the schema to the database.

    This should solve the Database principal or schema 'UserName' does not exist in this database. error.

  • use this handy script to change object owner to another user for objects ion the database

    select 'exec sp_changeobjectowner '''+user_name(Uid)+'.'+name+''','' ''' /*owner to assign to, dbo for example*/

    from sysobjects

    where user_name(uid) <> 'dbo' /*from owner */

    order by name

    paste the output into a new query window and execute to change owners

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

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

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

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