Bug or Feature

I noticed something when using Management Studio that I don't know if it's a bug or a feature. So if you have a login that's mapped to a user name in several databases, and you delete the login from the server using Management Studio, the user name is still left in the databases. This happens on a SQL 2000 or 2005 instance.

If I use Enterprise Manager and do the same function, the login is removed from the server as well as all databases where it was a valid user.

Any ideas?

Thanks,

Jason

It is by design as the user may own objects in some database, and some of the DBs may be offline or read-only. When you drop the login, the corresponding user in any DB will become an “orphan user”, and the behavior will be similar to restoring a database with user information without corresponding logins.

The sysadmin will have the opportunity to cleanup the “orphan users” by changing ownership of the objects in any given database and deleting the user or simply by mapping the orphan user to a different login using sp_change_Users_login.

I hope I was able to answer your question.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Okay, that explains it. Thanks for the info!

It would be nice if when the user owns an object, SQL Server changes the value in some flag for example, and then when the login is dropped from the server, SQL Server looks at that flag and say if it is set to 0, then drop the user from all databases, but if it is set to 1, drop the login but not the user from the database. This way it is less maintenance for the DBA to come through and clean up orphan users. Just my 2 cents.

Jason