102

Every SQL Server connection string I ever see looks something like this:

Data Source=MyLocalSqlServerInstance;Initial Catalog=My Nifty Database;
    Integrated Security=SSPI;

Do I need the Initial Catalog setting? (Apparently not, since the app I'm working on appears to work without it.)

Well, then, what's it for?

Ryan Lundy
  • 204,559
  • 37
  • 180
  • 211
  • 34
    Hmmm, I just discovered that my app put all my created tables in master. Oops. I may have just learned the answer to my question. – Ryan Lundy Dec 22 '09 at 23:12

3 Answers3

63

If the user name that is in the connection string has access to more then one database you have to specify the database you want the connection string to connect to. If your user has only one database available then you are correct that it doesn't matter. But it is good practice to put this in your connection string.

Avitus
  • 15,640
  • 6
  • 43
  • 53
  • 13
    Not quite true. The login may not have permissions on the default database. So, you need to change the database context on connection – gbn Dec 23 '09 at 07:30
  • This is required for the Entity Framework if you specify using the -ConnectionStringName parameter, so definitely good practice but also at times required! – James G Jan 01 '15 at 07:25
  • Can you please reword this answer? I still don't understand after reading through twice. – thatWiseGuy Jan 29 '19 at 23:33
36

This is the initial database of the data source when you connect.

Edited for clarity:

If you have multiple databases in your SQL Server instance and you don't want to use the default database, you need some way to specify which one you are going to use.

Andy West
  • 12,302
  • 4
  • 34
  • 52
  • 1
    The first part is correct. The second part is not correct. When you create the account it is assigned a default database which will be used if initial catalog is not specified. This generally defaults to master (for some unknown reason). – GrayWizardx Dec 22 '09 at 23:09
  • When I say "by default" I simply mean when you are not qualifying the database in your object names. In any case, I have clarified my answer. – Andy West Dec 22 '09 at 23:19
  • I like Andy's clarification, in fact his comment is helpful; doesn't it imply that if I *do* qualify every database in my object names, then the *Initial Catalog* is not as important / irrelevant? – Nate Anderson Oct 22 '20 at 19:44
16

Setting an Initial Catalog allows you to set the database that queries run on that connection will use by default. If you do not set this for a connection to a server in which multiple databases are present, in many cases you will be required to have a USE statement in every query in order to explicitly declare which database you are trying to run the query on. The Initial Catalog setting is a good way of explicitly declaring a default database.

jliles
  • 188
  • 1
  • 6