Mixed or Windows Authentication Only

Ok, at this time I would to discuss about the one but realy painful issue we have to face where we use SQL Authentication (Mixed).
Consider the following situation. You are using SQL Server (I speak for SQL Server 2000 SP4) Authentication(Mixed).
You also have a database and user within which is mapped to the SQL login. Now , you have been told to move the database on the new server. So you created a backup of the database , copied it to the new server as well as create a SQL Login with the same name as you had on the old one. Ok, now it is time to perform restore database, so everything went just fine but when theh users tried to connnect to the database (through application) on the new server they get the error. What happened? The thing is that SQL Server is 'losing' SIDs that mapped between a login and user when you created a new SQL Login on the new server (master..syslogins) and a database user (dbname..sysuser)that you brought within a database. So we have sp_change_users_login system stored procedure that takes care of re-mapping SID's.

However, that DOES NOT happen if you use Windows Authentication Only.That means SQL Server keeps SID's realtionship between Windows Login and User database although you move the database on the new server.

Just one more argument to use Windows Authentication Only.

In SQL Server 2005 SP2 MS has introduced some greate features on subject.
http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx.