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?
3 Answers
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:
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.