DROP LOGIN fails on SQL Server 2014

Posted on

Question :

We have a database with an owner ‘dbowner’. Our install procedure drops the login for user ‘dbowner’ and creates it again.

On any other version of SQL Server the following SQL query succeeds:

USE [master]
DROP LOGIN 'dbowner'

On SQL Server 2014 the same query returns the following error:

Login ‘dbowner’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

Can anyone explain why this would be different on SQL Server 2014? Dropping of login works on all other versions of SQL Server (2005, 2008 and 2012).

Is there any setting to force the same to work on SQL Server 2014?

Update:

I can replicate this with an example that uses Windows users. Any ideas?

CREATE LOGIN [compwinuser] FROM WINDOWS;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO [compwinuser];
GO
DROP LOGIN [compwinuser];
GO

Update 2:

Thank you for your comments. I agree that dropping the login of owner is not desirable (but works with Windows user everywhere except on SQL Server 2014). However our installation scripts currently depend on it and the extremely complex database installation procedure was made by some guy 15 years ago (he no longer works with us). It is an area that we like to avoid changing if possible (as long as it works).

As I see it, we have three options:

  1. Make SQL 2014 to act like the previous versions and leave the scripts as they are. We prefer this option and I posted here to see if this could be done.
  2. Change the owner to some other user, drop and create owner login and move the owner back. This is a hack but might work.
  3. Change the way we handle users and stop dropping login of the owner. This would be the correct way but also the biggest change.

Answer :

For SQL Authentication logins, this is no different in SQL Server 2014 than in previous versions. I just did this in 2008:

CREATE LOGIN dbowner WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO dbowner;
GO
DROP LOGIN dbowner;
GO

Msg 15174, Level 16, State 1

Login ‘dbowner’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

I can’t drop the database until I do this:

ALTER AUTHORIZATION ON DATABASE::mydb TO sa;

For Windows Authentication logins, As Dan pointed out, prior to SQL Server 2014, this check wasn’t performed for Windows logins, so you could leave databases in a weird state by dropping their owner. Your second option sounds best to me, and it only means adding the above line to your script. Another option is to just not make a Windows login the owner (I’m not sure why you’re making them owners instead of just giving them appropriate rights in the database), or stop dropping the logins as part of the process, or both.

Leave a Reply

Your email address will not be published.