3

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 [comp\winuser] FROM WINDOWS;
GO
CREATE DATABASE mydb;
GO
ALTER AUTHORIZATION ON DATABASE::mydb TO [comp\winuser];
GO
DROP LOGIN [comp\winuser];
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.
5
  • This isn't new in 2014. Check to make sure your other instances have the same setup. Apr 3, 2015 at 20:51
  • The same scripts are executed everywhere (hundreds of installations). I have provided an example above. Only SQL Server 2014 is affected.
    – matjazr
    Apr 3, 2015 at 21:56
  • 2
    If a login owns a database, you can't drop the login. Period. If you can, it's a bug, and you should write your script so that you only drop logins who don't own any databases. Apr 3, 2015 at 23:29
  • 1
    Perhaps there is confusion over membership in the 'db_owner' role, and actually being the owner of the database?
    – Hannah Vernon
    Apr 4, 2015 at 2:58
  • 1
    SQL 2014 fixes the bug that allowed one to drop Windows logins of database owners. I don't think option #1 is feasible, unless to Microsoft is willing to develop a hotfix with a trace flag to revert to the pre-SQL 2014 behavior. Despite what the legacy installer does today, the changes should be limited to T-SQL so I wouldn't expect either option 2 or 3 to be a big deal.
    – Dan Guzman
    Apr 4, 2015 at 12:58

1 Answer 1

6

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.

1
  • Thanks, I have replicated this with Windows user. Query is in updated question.
    – matjazr
    Apr 3, 2015 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.