Possible to set default schema from connection string?

Question

With SQL Server 2005 and 2008 is it possible to set the default schema from the connection string? It'd be a lot easier if we didn't have to manually set the schema with SQL code.

Barcroft

Answer 1

No, this is done at the database User level, not in the connection string.

For reference, here are all of the properties which can be set in a connection string: https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

Overpraise

Answer 2

You set the default schema based on the user who is logging in via the connection, not the connection itself.

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;

Reference:

• ALTER USER

Systemic

That would really suck for us though :/ – Barcroft

LMAO..I posted exactly the same thing :-) – Casavant

You mean you both came up with mary5 as a random name? Noway! – Wills

A very good idea. But is it possible to do this in AWS Redshift? – Improbable

Answer 3

change the default schema associated with that login

example

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing;

More detail here: http://msdn.microsoft.com/en-us/library/ms176060.aspx

Casavant

Answer 4

If when you say "Schema," you mean "Owner" (i.e. dbo), then I believe the selected answer is correct.

However, if you mean "Database" instead, which in some vendor's lingo means the same thing as "Schema," then I have provided some more info below.

In the link that TimS provided:

• All SQL Server SqlConnection properties

Scroll down to the row with these two properties:

Initial Catalog -or- Database

Here's an example connection string with a DEFAULT DATABASE:

Server=myServerName\myInstanceName,1433;Database=DEFAULT_DATABASE;User Id=myUsername;Password=myPassword;

See link below for more examples:

• Connection strings for SQL Server 2012

Telegony

Answer 5

I have had this problem too.
I removed Integrated Security=sspi; from my connection string and then it worked fine.

Razor