SQL: How to Programmatically assign sysadmin permission SYSTEM account?

In the SQL Servers prior to SQL version 2012, the settings for sysadmin in ‘NT Authority\System’ is checked by default but this option is not checked for SQL Server 2012 and above. This change caused problems with our C# application because it schedules a task (using the Windows Task Scheduler) to run the application (as a .exe file) that needs to connect to our MSSQL database.

The scheduled task has The SYSTEM account privileges.

But the sysadmin in System account was not checked in the Server Roles section of the Login Properties of our database.

As explained in What’s New in SQL Server Installation:

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

Microsoft SQL Server Docs

The Error that our application return when trying to make the connection was this:

Login failed for user 'Domain\ComputerName$'.    at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)   at System.Data.SqlClient.SqlConnection.Open() 

Looking at the ERRORLOG file under ‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS2017\MSSQL\Log’, I saw this error as the application was ran:

2019-07-31 12:00:28.47 Logon       Error: 18456, Severity: 14, State: 38.
2019-07-31 12:00:28.47 Logon       Login failed for user 'Domain\ComputerName$'. Reason: Failed to open the explicitly specified database 'myDatabase'. [CLIENT: ]

This link provided some good explanation for Troubleshooting error 18456.

State code 38 means: ‘Login valid but database unavailable (or login not permissioned)’

So after assigning ‘sysadmin’ permission to SQL server, the issue was fixed and the scheduled application could connect to the database.

How to programmatically grant ‘sysadmin’ permissions to ‘NT AUTHORITY\SYSTEM’:

-- The MSSQL Script to assign sysadmin permission to SYSTEM account:
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' 

Here’s the C# code that runs the above SQL script to assign ‘sysadmin’ permission to the System account:

    private static Logger logger = LogManager.GetLogger("General");
    public static string GrantSysadminPermission(string connectionString)
    {
        const string sql = @"
        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' ";
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();
            }
        }
        catch (SqlException ex)
        {                logger.Error("Error ...", ex);
             ...
        }
        return "";
    }

The connectionString passed to the above method is the connection string to connect to the MSSQL database, which is something like this:

connectionString = "Data Source=.\SQLEXPRESSName;Initial Catalog=databaseName;Integrated Security=True;User ID=;Password="