I changed the name of a database in Microsoft Sql Server 2005.  The database happened to be the default database for a user.  The next time the user tried to connect to the SQL server instance, they received the following error message: Cannot Open User Default Database.  Here is the fix.

SQL Server 2005

You can use the sqlcmd utility to change the default database in SQL Server 2005. To do this, follow these steps:

1. Click Start, click Run, type cmd, and then press ENTER.
2. Use one of the following methods, depending on the kind of authentication that the SQL Server login uses:

   If the SQL Server login uses Microsoft Windows authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

   sqlcmd –E -S InstanceName –d master

   If the SQL Server login uses SQL Server authentication to connect to the instance, type the following at the command prompt, and then press ENTER:

   sqlcmd -S InstanceName -d master -U SQLLogin -P Password

   Note InstanceName is a placeholder for the name of the SQL Server 2005 instance to which you are connecting. SQLLogin is a placeholder for the SQL Server login whose default database has been dropped. Password is a placeholder for the SQL Server login password.
3. At the sqlcmd prompt, type the following, and then press ENTER:
   ALTER LOGIN SQLLogin WITH DEFAULT_DATABASE = AvailDBName
   Note AvailDBName is a placeholder for the name of the existing database that can be accessed by the SQL Server login in the instance.
4. At the sqlcmd prompt, type GO, and then press ENTER.

• Custom Sort from Andy Warren.  How to get United States to appear first in a drop down list of countries. 
  
  Select CountryName
  From Countries
  order by
    Case when CountryName = 'United States' then 0 else 1 end,
    CountryName
  
   
• I was having trouble parsing an Update query that was using an inner join.  It turned out that I was missing the From clause in my update statement.

Update Tablename

Set Field1 = 100

Inner Join --Wrong

Update Tablename

Set Field1 = 100

Fom Tablename

Inner Join --Correct 

• In Sequel Server Reporting Services (SSRS), how to hide a textbox (follow link, go to using Complex Expressions, about halfway down within Complex Expressions, Hide a text box on the design surface...) based on a boolean field value in the recordset:

Open Textbox properties Dialaog box, click on Visibility tab, and enter =Not fields!VoidFlag.Value in the Expression box.   

I was setting up security for general users on a production Microsoft SQL database. The security configuration needed to allow the users to execute stored procedures called from a WPF application. Here are the steps I took using Windows Authentication.

1) At the database security level, I did not assign the users to any specific database roles, so the users, by default, were assigned to the public database role, which provides minimum permissions and privileges.

2) Granted Execute Permissions on the stored procedures:  Grant Exec On StoredProcedureName To User/AcitveDirectoryGroup  (We assigned the users to an Active Directory group).   Below is some sql code to simplify the process:

SELECT 'GRANT EXEC ON ' + name + ' TO ' + '[ ActiveDirectoryGroupName ]'
FROM sysobjects
WHERE type = 'P' AND category = 0 And name not Like 'sp_%'
Order By name

3) Deny view capabilities to the public role:

Use DatabaseName
GO
DENY VIEW DEFINITION TO public;  

Further Refinded:

I subsequently found other ways to grant Exec privileges through the creation of a db_executor role.

 a) Grant Exec at the database level through a db_executor role      
              
     CREATE ROLE db_executor
     GRANT EXECUTE TO db_executor   exec sp_addrolemember 'db_executor','YourUser'  
 
b)  Grant Exec at the schema level

       GRANT EXECUTE on schema::dbo TO db_executor