In the course of deploying an app to a client, I had to set up a SQL Express database on the client's workstation. I used the SQL Express Utility mentioned here to run scripts that created the database, tables, procedures, etc. But when generating the script to create the user for my app, SQL Server Management Studio took some liberties in the name of security, as indicated by the generated comments.
/****** Object: Login [myuser] Script Date: 11/14/2007 18:48:44 ******/
/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [myuser] Script Date: 11/14/2007 18:48:44 ******/
CREATE LOGIN [myuser] WITH PASSWORD=N'I?òC5)?ð¸D®È-tð× _?c¶8??', DEFAULT_DATABASE=[MyDatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER LOGIN [myuser] DISABLE
I'm not sure why I would want to disable a login immediately after creating it on the server, but deleting that line from the script is easy enough. What would prove a little tougher is finding out what the random password is and resetting it. Keep in mind that on the box I'm deploying to, I am installing SQL Express, but not the full Management Studio. I needed a script that I could run (using the aforementioned utility) to create the user with the same password that is in my app.config file.
Fortunately I found an article on Microsoft's support site that addressed just such a situation: http://support.microsoft.com/kb/918992/
The solution involves creating two stored procedures on your source SQL Server, then running the second one. The output script generated by the second stored procedure is the login script. The login script creates the logins that have the original Security Identifier (SID) and the original password.
CREATE LOGIN [myuser] WITH PASSWORD = 0x01009CEDB024F69AC02E1CC8ACE164CCE0EA5B29C650287320C3 HASHED, SID = 0x83EDB2791611A14EB45664682197A66B, DEFAULT_DATABASE = [MyDatabase], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
Then you simply run the login script on the destination SQL Server to create the login with the correct password.