Good Question! This is one of those error messages that you will eventually encounter in SQL Server if you work with it enough. A login’s default database is a delicate thing that will prevent them from being able to logon if there is a problem. The default database defines which database the user will be automatically logged into if they connect and they don’t specify a database. Several things can cause the above error message. The most common is a login that does not have access to the database that is configured as its default. This is easily remedied; either set up a corresponding user for the login in their default database or change the default to a database to which the login has access. This error will also rear its ugly head if you drop a database that was configured as the user’s default database. There is no check when a database is dropped to make sure it won’t affect users, so dropping someone’s default database will leave them in the dark when it comes to logging in. As I said, this only happens if the user’s connection method does not specify a database. Many applications will provide the database name and will be immune to the issue. The fix is simple, make sure the logon has access to their default database and make sure the default database exists.

This problem can get a little worse if you break the default database of your system administrator accounts, such as your Windows account that has ‘sa’ level rights or the ‘sa’ login itself (and no other logins have the permissions to fix the problem). In this case, the fix is still simple, but you need to use an application that allows you to specify a database, such as SQLCMD. SQLCMD is a command-line tool that comes with SQL Server 2005 and 2008. When you login, you can specify a database to use that will override the default. So to fix your problem, login with SQLCMD as follows:

sqlcmd -S sql2008 –U sa -P password -d master

This will let you login and use the master database despite the incorrect default database. To fix your login simply run the following query from the SQLCMD command prompt (you can use any database that exists and to which the login has access, master is used as an example):

ALTER LOGIN sa WITH DEFAULT_DATABASE=master

That’s it, the next time you login your new default database of master, or whatever you specified, will be used.

They have been around forever, but have you really ever implemented Application Roles in SQL Server? For that matter, do you really know what they are and how to use them? In this short tip, I hope to explain just that.

First off, we need to talk briefly about how application security can be implemented in SQL Server. There is some debate over which model is better and I am not endorsing any specific one here. Regardless of whether you use SQL Server Logins or Windows Authentication, you still have to decide whether an application will use a single login to access SQL Server (and all appropriate database objects), or allow each individual user to have their own login. They each have their very own sets of pros and cons, but I want to focus on a specific con of each user having their own login. The biggest issue is that each user login has access to your server and to one or more databases. Does the user need to delete data as part of their job? If so, they will have this right whether they log in via an application or directly to the server. Often, the application controls what can and cannot be deleted based on a set of business rules; these rules usually don’t exist on the SQL Server itself. In short, if each user has their own login, they can access SQL Server directly and potentially cause some damage.

This brings us to application roles. You create them and assign permissions to them just like regular database roles but you can’t put users in them. Instead, the goal of application role is to provide a best of both worlds scenario for application and user security. Here’s how they work. You set up each user to have an account on the SQL Server with practically no rights. All they should be able to do is login to the server and run a system stored procedure called sp_setapprole. This procedure accepts a couple parameters, including the name and password for the application role. Running sp_setapprole will immediately endow the user with all the permissions that you set up on the application role for the current session only.

So what does this mean for security? As long as the password for activating the application role is only known to the application, your users will not have any rights when they login to the SQL Server directly. In order to have the permissions they need, they will be required to use the application that knows the password and can unlock the permission for the application role. Now you can have SQL Server manage individual logins and still have a secure environment that uses the rules and filters in place within your applications.

You ever find yourself with the need to copy SQL Server logins from one server to another? Maybe you are setting up a failover site, building a replacement server, setting up a reporting instance, or maybe you just want to backup the logins just in case. If you are using Windows Logins, this is a simple matter of scripting the login and applying it to the other server. Copying SQL Server Logins from one box to another is a bit trickier because SQL Server stores and manages the password. So just how do you copy the login and preserve the password? I am glad you asked.

Understanding Login Components

To successfully copy a login from one server to another, you will need to ensure that the copy has the same SID and password. The link between database users and logins is done with the logins SID, if this is different on the new server than any databases you copy over will contain orphaned users. To ensure that both the SID and the password are the same, Microsoft has written a stored procedure to aid in our transfer.

SP_HELP_REVLOGIN

SP_HELP_REVLOGIN is a stored procedure that will return a complete list of the logins that exists on you SQL Server in a script that can be run to recreate them. This script does not exist on your SQL Server by default, you must create with the code provided by Microsoft in KB article 918992 here http://support.microsoft.com/kb/918992/. Once you have create the procedures you can easily generate the create statements that allow you to copy your logins.

As an example, I created a new login on my local instance of SQL Server called SQLScript with a password of scriptme. Now I can run SP_HELP_REVLOGIN as follows:

sp_help_revlogin 'SQLScript'

RESULTS:

/* sp_help_revlogin script

** Generated Oct 30 2007 9:23AM on laptop1 */

-- Login: SQLScript

CREATE LOGIN [SQLScript] WITH PASSWORD = 0x0100B642C5A8BC6778ECE4710ED3DC8D70E0EA31B6DF6B122756 HASHED, SID = 0x80525EB475F8414FB32D627BB876F213, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see, I now have the syntax I need to recreate the login on another box. The SID will be forced to the same value and the passwords will match by virtue of this statement providing the hashed version of the password. If you need to copy all the logins, SQL Server and Windows Logins, you can run SP_HELP_REVLOGIN with no parameters.

The new SQL Server installation is so different, I thought I would take a second to write about it. For starters, the whole thing is now called the SQL Server Installation Center.

This tool is broken down into seven sections which include the following:

• Planning - This section gives you access to documentation and allows you to run the upgrade advisor.
• Installation - This is where the rubber meets the road. You can install stand-alone instances, clusters, add-nodes to existing clusters, and even start upgrades from 2000 or 2005.
• Maintenance - Here you can change your edition of SQL Server (that's right - change - did you install developer edition on production by accident, here you can change to to enterprise), repair an installation, or remove a node from a cluster.
• Tools - Access to a few handy tools such as the System Configuration Checker, a report to tell you what SQL Server features are installed, and an SSIS upgrade wizard.
• Resources - Links to helpful documentation on SQL Server
• Advanced - This section lets you install SQL Server based on a configuration file, perform advanced cluster prep, or complete a cluster from already cluster-prepared SQL Servers.
• Options - Allows you to specify the architecture (x86, x64, or ia64) and the location of the install media.

Enough about the Installation Center, let's take a look at the stand alone installation. I am not going to walk through step-by-step, I just want to highlight some of the new features that I think are pretty cool.  This is the face of the new instillation wizard.

The first few screens are just some checks and then the installation of the prerequisites. After that's done we get to the meat of the installation. Even here, the first few screens are pretty straight forward asking if you are installing a new instance or changing an existing instance and another screen allowing you to enter you product key. The first screen that grabbed my attention was the Instance Configuration. Here you not only specify and instance name, but you an also specify the instance ID. The instance ID is used in folder naming instead of random numbers. By default the ID will be the name of your instance.

The next few screens are your standard drive space overview and service account set up, nothing really new here. Ah, but then we get to the Database Engine Configuration screen which abounds with new options for security, file locations, and the new FILESTREAM type. The first tab, shown below, lets you set the authentication mode and set up accounts that will be administrators of SQL Server. Gone are the days of BUILTIN\Administrators and here are the days of telling SQL Server who you want to be administrators.

The second tab, Data Directories, allows you to set up the default locations of some common files used by SQL Server. This really gives you a level of control that was previous a registry hack, done manually, or done after the installation was complete.  You can set up individual locations for the following:

• Data Root
• User Database Files
• User Database Log Files
• Temp DB Data
• Temp DB Log
• Backup Files

Last but not least, the third tab allows you to enabled FILESTREAM for T-SQL access. I won't get into FILESTREAM here, but it is a feature of SQL Server 2008 worth your further investigation.

That's the bulk of the installation....one more check to look for problems that might cause the installation to fail and your off and running.

I know the installation wizard isn't the sexiest new thing in SQL Server 2008, but there are some neat new things that made me happy so I wanted to share my rather geeky bliss.