How to create a SQL Server login for a service acc

问题:

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...

回答1:

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.

回答2:

This works for me on SQL Server 2008R2:

IF (SELECT COUNT(*)  FROM sys.server_principals WHERE sid=0x010100000000000514000000)=0  
    BEGIN
        DECLARE @cmd VARCHAR(200)
        SET @cmd = N'CREATE LOGIN [' + SUSER_SNAME(0x010100000000000514000000) + '] FROM WINDOWS'
        EXEC (@cmd)
        PRINT 'Created network service server login from SID'
    END
ELSE
        PRINT 'Network service account found from SID, server login not created'

Can anybody verify this on a non-English Windows - German, Italian?

回答3:

You should be able to change the language so that this works.

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

I work on servers with Swiss German locale with us_english for SQL Server and we've never had to do this kind of thing. So I'm guessing that SQL Server takes it's cue from it's own language settings.

HTH