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?
I can replicate this with an example that uses Windows users. Any ideas?
CREATE LOGIN [comp\winuser] FROM WINDOWS; GO CREATE DATABASE mydb; GO ALTER AUTHORIZATION ON DATABASE::mydb TO [comp\winuser]; GO DROP LOGIN [comp\winuser]; GO
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:
- 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.
- Change the owner to some other user, drop and create owner login and move the owner back. This is a hack but might work.
- Change the way we handle users and stop dropping login of the owner. This would be the correct way but also the biggest change.