Each time you create a new login in SQL Server, you have the choice of assigning a default language to that login. If you don’t do this, the login will use the default language as specified in the default language server configuration option.

This article demonstrates how to set the default language server configuration option in SQL Server, using T-SQL.

First, Check the Server’s Default Language Settings

Before we go ahead and change anything, we should check to see what the current settings are.

The sp_configure stored procedure allows you to view or change global configuration settings for the current server.

To return all configuration options you can execute this stored procedure without passing any arguments. Like this:

EXEC sp_configure;

However, that returns quite a large result set.

Seeing as we’re only interested in the default language setting, we can run the following code:

EXEC sp_configure @configname='default language';

And on my test environment, that returns the following:

+------------------+-----------+-----------+----------------+-------------+
| name             | minimum   | maximum   | config_value   | run_value   |
|------------------+-----------+-----------+----------------+-------------|
| default language | 0         | 9999      | 0              | 0           |
+------------------+-----------+-----------+----------------+-------------+

The main values we’re interested in is config_value and run_value. In this case, both values are 0, which is the language ID for us_english.

We can change those using the following code:

EXEC sp_configure 'default language', 5;
RECONFIGURE;

Result:

Configuration option 'default language' changed from 0 to 5. Run the RECONFIGURE statement to install. 

Note that the values for config_value and run_value are not automatically equivalent. After updating a configuration setting by using sp_configure, you must update run_value by using either RECONFIGURE or RECONFIGURE WITH OVERRIDE. Given we already did that in this example, we’re good to go.

So when we check the settings again, we can see the new values:

EXEC sp_configure @configname='default language';

Result:

+------------------+-----------+-----------+----------------+-------------+
| name             | minimum   | maximum   | config_value   | run_value   |
|------------------+-----------+-----------+----------------+-------------|
| default language | 0         | 9999      | 5              | 5           |
+------------------+-----------+-----------+----------------+-------------+

In this case, we changed the values to 5, which is the language ID for Español (Spanish).

You can also query the sys.configurations view to return the values of the server configuration options if you prefer.

How to Find the Language ID

If you don’t know the language ID of the language you need to change to, you can execute the sp_helplanguage stored procedure. You can run it with no arguments, in which case all languages will be returned, or you can provide a language name or alias so that only that language is returned.

Here’s an example:

EXEC sp_helplanguage Spanish;

Here’s the result I get when I run that in mssql-cli:

-[ RECORD 1 ]-------------------------
langid      | 5
dateformat  | dmy
datefirst   | 1
upgrade     | 0
name        | Español
alias       | Spanish
months      | Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octub
shortmonths | Ene,Feb,Mar,Abr,May,Jun,Jul,Ago,Sep,Oct,Nov,Dic
days        | Lunes,Martes,Miércoles,Jueves,Viernes,Sábado,Domingo
lcid        | 3082
msglangid   | 3082

And we can see that the language ID for Spanish is 5. So that’s the value we provide when executing sp_configure to change the default language to Spanish.

So now when we create a new login, its default language will be Spanish (unless we explicitly provide a default language when creating the login).

Create a New Login – Without Specifying the Default Language

So let’s create a new login without specifying a default language:

CREATE LOGIN Julio   
    WITH PASSWORD = 't35Tin9345!'

And now check the default language for that login:

SELECT default_language_name 
FROM master.sys.server_principals
WHERE name = 'Julio';

We get the following result:

+-------------------------+
| default_language_name   |
|-------------------------|
| Español                 |
+-------------------------+

Because we didn’t specify a default language for this login, it uses the default language as configured at the server level.

Create a New Login – With a Default Language

But if we specify a default language when we create the login:

CREATE LOGIN Einstein   
    WITH PASSWORD = 't35Tin9345!',
    DEFAULT_LANGUAGE = German;

And then query sys.server_principals again:

SELECT default_language_name 
FROM master.sys.server_principals
WHERE name = 'Einstein';

We get the following result:

+-------------------------+
| default_language_name   |
|-------------------------|
| German                  |
+-------------------------+

So the server configuration is only used when you don’t explicitly specify a default language for the individual login.

Note that a user can change the language in use within their session. Just because they have a default language, it doesn’t mean that they’re stuck with it. For more information, see 3 Ways to Get the Language of the Current Session in SQL Server (T-SQL) and How to Set the Current Language in SQL Server (T-SQL).