Change SQL database Authorization from MIXED MODE to User Name and Password

MarvinEMarshall used Ask the Experts™
I recently setup SQL 2008 R2 and set the authoriization to MIXED MODE.  The database I moved to this new Server has the database set to User Name and Password, which of course I have both the User Name and Password.  I would like to change the SQL 2008 copy to the same User Name and Password.  The main reason for doing so is so my existing code in web.config should work with no changes.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Marten RuneSQL Expert/Infrastructure Architect

Your server is already in Mixed mode. Which supports a username and password. You either migrate the account using sp_revlogin or create a new account on the server, with the same name, and password. after that fix the mapping in the database using sp_change_users_login

Google any of them and you'll find tons of examples.


I'm following your advice.  After reading some, I did this query -
    exec sp_helplogins
It listed master database and the one I just Restored - sql_song1 with the User Name of sa and another one of leon (leon is a programmer I hired many years ago).
I certainly don't want to lock myself out of the my own database.  Given the info above and it's a large propritory database, I'm a little reluctant to do trial and errors.  What would you advise to do next?  Thanks Marvin E Marshall
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

>> I recently setup SQL 2008 R2 and set the authoriization to MIXED MODE.

So you have Mixed Mode Authentication enabled to work for SQL Logins.
But I believe you haven't transferred the logins from your 2005 server using the script below:

Once you transfer logins, you need not worry about logins..
SQL Expert/Infrastructure Architect

USE sql_song1;
exec sp_change_users_login 'Update_One', 'leon', 'leon';

This solution assusmes you created a SQL account named leon within the SQL server, not the OS!

The other solution sp_revlogin mentioned by me and rrjeagan17 has to do with internal SQL Sid. This stays the same between both servers when migrating accounts utilizing sp_help_revlogin. Lookat:

Regards Marten

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial