Sql-server – Grant sysadmin permissions to ‘NT AUTHORITY\SYSTEM’

scriptingsql serversql-server-2012

For SQL Server 2012 and above, as What's New in SQL Server Installation states:

BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.

In my case this has become a problem: I have a WiX installer which uses a Custom Action C++ code to setup database data (create the database, views, procedures, data etc.). Custom Action runs as user NT AUTHORITY\SYSTEM, but this account does not have permission to execute CREATE DATABASE scripts.

  1. Is it correct to give sysadmin role to NT AUTHORITY\SYSTEM while installing software? Or maybe there is a better solution?

  2. If it is correct, is it possible to find the proper user name for NT AUTHORITY\SYSTEM? I need this name in order to change permissions*. In different locales this name is different, for example, NT AUTHORITY\SYSTEM or NT AUTHORITY\СИСТЕМА.

Adding NT AUTHORITY\SYSTEM to the sysadmin role:

IF NOT EXISTS
(
    SELECT name
    FROM master.sys.server_principals 
    WHERE IS_SRVROLEMEMBER ('sysadmin', name) = 1 
    AND name LIKE 'NT AUTHORITY\SYSTEM'
)
EXEC master..sp_addsrvrolemember
    @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'

Searching sys.server_principals for name LIKE 'NT AUTHORITY\%' will not work because sometimes there will be more than one NT AUTHORITY\... user. I encountered a case like that on one of my virtual machines.

Best Answer

You can get the name of well-known security identifiers (SIDs) using a PowerShell script:

$objSID = New-Object System.Security.Principal.SecurityIdentifier ("S-1-5-18")
$objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value

Personally I never had to do this from C/C++ code. See the MSDN article about the WINAPI or System.Security namespace for managed code.

There is a compact example in the Stack Overflow Q & A WinAPI LookupAccountSid from an .evt file by Remy Lebeau:

static const DWORD MAX_BUFF_SIZE = 256;

wstring userNameFromSid(SID userSid, wstring computerName)
{
    wchar_t buffName[MAX_BUFF_SIZE];
    DWORD buffNameSize = MAX_BUFF_SIZE;
    wchar_t buffDomain[MAX_BUFF_SIZE];
    DWORD buffDomainSize = MAX_BUFF_SIZE;
    SID_NAME_USE SidType;

    if (LookupAccountSid(!computerName.empty() ? computerName.c_str() : NULL, &userSid, buffName, &buffNameSize, buffDomain, &buffDomainSize, &SidType))
    {
        return buffName;
    }

    /*Here some code to print error in a Message box*/
    return L"";
}

Is there any problem placing NT AUTHORITY\SYSTEM in the SQL Server sysadmin role when installing software from a reliable source? There should be no problem, I hope, but who knows...

Related Question