SQL Server Performance

How to change or delete database owner ?

Discussion in 'Contribute Your SQL Server Scripts' started by techbabu303, Jan 14, 2008.

  1. techbabu303 New Member

    How to delete database owner ?
    I have observed during implementation projects most of the times consultants want to delete the database owner and create new one to meet client requirements or if that user has left organization. During these times they would try delete the user and often give up, they would then be tempted to create new login to work around it.
    So for those who are falling for the workaround they an follow the recommendation below
    and become efficient administrators.
    Applies To:
    Microsoft SQL Server 2000/2005
    Error Message :
    When you try to delete database owner from database you get following error message
    SQL 2005
    Deleting Server logins does not delete the database users assciated with logins. To complete the process, delete the users in each database.It may be necessary to first transfer the ownership schemas to new users.
    SQL 2000
    Error 15174 : Login 'Charles' is aliased or mapped to a user in one or more database(s).
    Drop User or alias before dripping the login.
    Database Name : NORTHWIND

    Check List:
    Open the query analyzer or SQL server 2005 Management Studio , check on the table names they all should display as dbo.<table name> if they are displayed as Username.<tablename> then you need to change the object owner first to new user who is supposed to be database owner. If you fail to do this then you cannot create index or views on these tables through the application.
    To change the object owner you need run the system stored procedure as listed below
    sp_changeobjectowner ‘<object name/table name> ' ,'<new db owner>'
    Steps to Delete Existing DB Owner :
    Step 1 : Change database owner to new user using the system stored procedure as below
    Use Northwind
    sp_changedbowner ‘ Charles'
    Step 2 : Delete the old database owner user using the command
    Use Master
    sp_droplogin ‘Victoria'
    Note : You cannot drop the login unless you have remove all refrences to objects it owns in database as mentioned in the check list section above
    Alternatively you can delete the login from Enterprise Manager à Security in SQL 2000 or Microsoft SQL server Management Studio à Security in SQL 2005.
  2. satya Moderator

    This can into Tips section, why not send it to Webmaster and get credit for that... appreciate your knowledge sharing.

Share This Page