6

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.

  • 2
    Rather than granting sa privileges what you can do is find out what WiX installer needs to do and for that what permissions are needed. Grant those and you should be good to go. To start with you can grant dbcreator server role, install. If it errors out with permissions errors then grant again whatever is necessary. The other option is grant 'sa' rights temporarily, do your install and once done revoke the permissions. – SQLPRODDBA Jun 25 '16 at 6:12
2

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

  • 1
    If you're going to make obscure configuration changes like this at install time: I would strongly advise you to inform the user that you are making those changes. -- Additionally, I would consider rolling back those changes when the installer completes, so as to leave their machine in a pristine state. – BrainSlugs83 May 24 '17 at 20:05

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.