asked on

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

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.

Marten Rune

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.

MarvinEMarshall

ASKER

martenrune,
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 R

>> 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:

http://support.microsoft.com/kb/246133

Once you transfer logins, you need not worry about logins..

ASKER CERTIFIED SOLUTION

Marten Rune

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial