How to create a SQL Server login for a service account on a non-English Windows?

sql-server tsql locale windows-server-2008-r2
MarnixKlooster ReinstateMonica picture MarnixKlooster ReinstateMonica · Jun 27, 2011 · Viewed 10.2k times · Source

Given a Windows Server 2008 R2 system, where the service accounts use a non-English display language, with SQL Server 2008, the following Transact-SQL statement fails:

CREATE LOGIN [NT AUTHORITY\NETWORK SERVICE] FROM WINDOWS ...

with the following error:

Windows NT user or group 'NT AUTHORITY\NETWORK SERVICE' not found. Check the name again.

(or the same message in the non-English display language for the service accounts, depending on the context).

This same statement succeeds if the service accounts use English as their display language.

The reason seems clear: on, e.g., a German system the display name for this account is NT-AUTORITÄT\NETZWERKDIENST, and the name NT AUTHORITY\NETWORK SERVICE (with a space) is not recognized. Also the non-localized name NT AUTHORITY\NETWORKSERVICE (no space) does not work.

My question: How should I rewrite the above statement so that it works irrespective of the display language? Or am I forced to find out the localized name (in InstallScript in my case)? Then I can use

CREATE LOGIN [NT-AUTORITÄT\NETZWERKDIENST] FROM WINDOWS

which does work...

Answer

MarnixKlooster ReinstateMonica picture MarnixKlooster ReinstateMonica · Jul 23, 2011

From what I've tried, and from what I read in a German forum thread entitled "Well-known SID im SQL Server nutzen", this is not possible. Apparently unfortunately SQL Server's CREATE LOGIN was designed to accept only a localized name in DOMAIN\username format.

A hint, from that same thread, is to look at section "Localized Service Names" in "Setting Up Windows Service Accounts" for the localized names that need to be used in a CREATE LOGIN statement.

The only alternative is to try and find out the system language of the Windows system running SQL Server, then use the "Localized Service Names" table to find the localized service account name, and use that to create a working CREATE LOGIN statement.

Related questions

How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
OR is not supported with CASE Statement in SQL Server

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this? CASE ebv.db_no WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' ELSE 'WECS 9520' END as wecs_system

Read More