none
How to drop a user from database and transfer ownership on SQL 2008 R2?

    Question

  • Hi,

    I am running SQL Server 2008 R2 Ent.

    I have some SQL Login accounts which are not used any more and i want to get rid of those.

    when i try to delete the account i get the following error.

    1 - How do i check on which all DBs this user is associated?

    2 - How do i check on which all DBs this user is dbo?

    Please suggest.

    Regards,

    maqsood

    


    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Thursday, May 03, 2012 12:02 PM

Answers

  • What you really want is to know which databases the logins own.

    SELECT sys.databases.name, sys.server_principals.name
    FROM sys.databases
    JOIN sys.server_principals
    ON sys.databases.owner_sid = sys.server_principals.sid

    This is not the same as being dbo. That is, there can be multiple people who are dbo, but only one login can own the database. (For example, all members of the sysadmin fixed server role, enter each database as dbo.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, May 03, 2012 4:26 PM
  • can you please suggest how to transfer ownership to other SQL login or to default user SA so that i can delete this login?

    You can transfer database ownership with ALTER AUTHORIZATION:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, May 07, 2012 12:16 PM
    Moderator

All replies

  • What you really want is to know which databases the logins own.

    SELECT sys.databases.name, sys.server_principals.name
    FROM sys.databases
    JOIN sys.server_principals
    ON sys.databases.owner_sid = sys.server_principals.sid

    This is not the same as being dbo. That is, there can be multiple people who are dbo, but only one login can own the database. (For example, all members of the sysadmin fixed server role, enter each database as dbo.)


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, May 03, 2012 4:26 PM
  • can you please suggest how to transfer ownership to other SQL login or to default user SA so that i can delete this login?

    You can transfer database ownership with ALTER AUTHORIZATION:

    ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Monday, May 07, 2012 12:16 PM
    Moderator
  • Thanks for your help.

    Maqsood Mohammed Senior Systems Engineer MCITP-Enterprise Admin & ITILv3 Foundation Certified

    Monday, May 07, 2012 12:59 PM