Login change from Windows to SQL Server Authentication possible?

Question

I created a login with Windows Authentication. The properties window for it has Server Authentication section greyed out.

Can you switch the user to SQL Server Authentication? The server is set for SQL Server and Windows Authentication mode.

Answer 1

The short answer is no. There is a way though. If you are wanting to retain the database user object permissions this is a tough one, and you will have to script the user permissions (assuming you did not use database roles making your life exponentially easier). If you attempt to map a user that was originally mapped to a Windows Login over to a Sql Login you will receive the following error.

ALTER USER [old_windows_Login_based_user]
WITH login = [new_Sql_login]

Yields:

Msg 33017, Level 16, State 1, Line 1
Cannot remap a user of one type to a login of a different type. For example, a SQL user        
must be mapped to a SQL login; it cannot be remapped to a Windows login.

So what you will end up doing is dropping and recreating the login and user with the different authentication method.