Sql-server – How to SUSER_SNAME return the localized user name

loginssql servert-sql

I need to assign network service user to SQL Server database programmatically, so I am running this script:

USE [MyDB]
GO

DECLARE @user nvarchar(50)
DECLARE @SQLStatement nvarchar(500)
SET @user = SUSER_SNAME(0x010100000000000514000000);
SET @SQLStatement =
N'IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = ''' + @user + ''')
  BEGIN
      CREATE USER ' + quotename(@user) + ' FOR LOGIN ' + quotename(@user) + N' WITH DEFAULT_SCHEMA=[dbo]
      ALTER ROLE [db_owner] ADD MEMBER ' + quotename(@user) + '
  END'
EXEC sp_executesql @SQLStatement;
GO

That does not work. The problem is that, when running

PRINT SUSER_SNAME(0x010100000000000514000000)

This is returned:

NT AUTHORITY\NETWORK SERVICE

That is wrong, since my system is in Spanish. When I add that user manually using SQL Server Manager and then I see the users in the database, that user is actually added as:

NT AUTHORITY\Servicio de red

Is there a way to add Network service account independent of language?

Best Answer

Since you already have the SID — a value that does not change based on culture — you might as well use it:

DECLARE @Login sysname,
        @DelimitedLogin NVARCHAR(258),
        @SID VARBINARY(85);

SET @SID = 0x010100000000000514000000;

SELECT @Login = sp.[name]
FROM   sys.server_principals sp
WHERE  sp.[sid] = @SID;

SET @DelimitedLogin =  QUOTENAME(@Login);

IF (NOT EXISTS(
        SELECT *
        FROM   sys.database_principals dp
        WHERE  dp.[sid] = @SID
              )
    AND @Login IS NOT NULL
   )
BEGIN
  EXEC(N'
CREATE USER ' + @DelimitedLogin + N' FOR LOGIN ' + @DelimitedLogin
  + N' WITH DEFAULT_SCHEMA = [dbo];
ALTER ROLE [db_owner] ADD MEMBER ' + @DelimitedLogin + N';
');
END;
GO
Related Question