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?
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
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: