3

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?

  • Do you get an actual error? I don't think SQL Server keeps track of localisations of windows logins. It just associates their SID, and lets the OS do it's thing. From SQL Servers point of view. It doesn't matter whether it's in Italian, Spanish, German or English. – D Kramer Feb 17 at 14:12
  • @DKramer it seems it does matter. When that user is not already in the database, it is created as NT AUTHORITY\NETWORK SERVICE which is wrong. When I add it using SQL Server Manager, the added user is NT AUTHORITY\Servicio de Red. If that user already exist, the error is The login already has an account with another username.. On the other hand, since the added user is NT AUTHORITY\Servicio de Red, that IF statement always evaluates to TRUE. – jstuardo Feb 17 at 14:21
  • Your script works for me on a German installation where @user is returned as NT-AUTORITÄT\Netzwerkdienst. Strange. – John aka hot2use Feb 17 at 15:21
  • @Johnakahot2use Yes... it worked in my PC before... in customer's PC also.... but after some Windows 10 update, it stopped working in my PC. The rule is: if in my PC stopped working, it is more likely that in customer's PC will not work also. That's why I need this to be not language dependent, – jstuardo Feb 17 at 20:00
  • Can you tag for SQL Server version? I'm sure of the exact terminology, but what are the "regional settings" of the user that the script is running as? – Colin 't Hart Feb 19 at 13:38
1

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.