One of my favorite new features with SQL Server 2008 is the addition of intellisense to the query windows in SQL Server Management Studio.  But I have been a little bit frustrated by its behavior at times.  For example, after I create a new table, I often want to write stored procedures that use that table.  But the query window would not show the table or its columns in intellisense.  Once I typed out the table and column names completely, they would have the red squiggly underline indicating that they were invalid.  The query would execute fine, but the red squigglies would remain. 

I just discovered that this problem was not overlooked by Microsoft.  They included a menu option to refresh the local cache for intellisense.  Find it under Edit > Intellisense > Refresh Local Cache, or just use the keyboard shortcut (Ctrl + Shift + R).  I probably should have found this sooner, and many of you may have already known about it, but for those of you who have felt the same frustation, I hope this helps.

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.