One of the Database Ownerhas been Removed and corresponding property changes in sql server

Question

A database has been created on SQL 2008 by a user named user1 ,on dec 2013.Today ,we are going to remove that user(/owner) from the server.What will be the owner property of the database show then?

Answer 1

You have to change the ownership of each database that that user owns before you can delete the user.

Change ownership using SSMS or you can use T-SQL

use database_name
EXEC sp_changedbowner 'newowner'

Following query allows you to query all databases owned by a particular login.

use master
select database_id, name, suser_sname(owner_sid) from sys.databases where suser_sname(owner_sid) = 'logintoremove'

You could then use this information to bulk update ownership.

use master
DECLARE @name VARCHAR (50) -- database name
DECLARE @oldOwner VARCHAR (50)
DECLARE @newOwner VARCHAR (50)

SET @oldOwner = 'oldownerlogin'
SET @newOwner = 'newownerlogin'

BEGIN
    DECLARE db_cursor CURSOR FOR
    select name from sys.databases where suser_sname(owner_sid) = @oldOwner
END

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
    BEGIN
        EXEC(
            'USE '+@name +';

            EXEC sp_changedbowner ''' +@newOwner+ '''
            ' )
        END

    FETCH NEXT FROM db_cursor INTO @name
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

Answer 2

As the previous answer states, you will get the message:

Login 'userName' owns one or more database(s). Change the owner of the database(s) before dropping the login.

sp_changedbowner has been deprecated (See link) and will therefore be removed, therefore instead of this you should be using the following to change the owner before dropping the login:

ALTER AUTHORIZATION ON DATABASE::DBname TO newlogin

Once you have done this, you can then drop the login.

ALTER AUTHORIZATION syntax can be viewed here:

ALTER AUTHORIZATION

Answer 3

OLD post but thought it may help others.

In my case i removed ownership of all databases and still couldn't drop the user.

Turns out the user was the owner of some SQL jobs. So check and remove ownership from SQL jobs as well DBO from any database.