Bug or Feature?

When upgrading a database from MSDE to Visual Studio 2005 Express, I've noticed that the system tables in the MSDB database don't get upgraded. For example, the SYSSCHEDULES table doesn't get added. Is this by design or a bug?

-Doug

Belatedly moving this thread to the Express forum for an answer.|||

Given that there is no SQL Agent support in Express this may well be by design

|||

Hi Doug,

This has been addressed in SP1, which you can get from http://msdn.microsoft.com/vstudio/express/sql/download.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||I did find a fix to this problem. If I run the INSTMSDB.SQL script from the install directory on the updgraded database, it will install all of the new MSDB system tables.

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