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

Answer 1

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

Answer 2

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/

Answer 3

Thanks for your help.

---

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