Understanding N'[Uncategorized (Local)] in SQL Server Agent

sql serversql-server-agent

The following code is seen in several Q&A code snippets here (and elsewhere), I am trying to translate the section N'[Uncategorized (Local)]

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', 
       @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

Examples

I have looked at Microsoft EXECUTE (Transact-SQL)

'name'

Is a valid user or login name. name must be a member of the sysadmin fixed server role or exist as a principal in sys.database_principals or sys.server_principals, respectively.
name cannot be a built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

and

[N] 'command_string'

Is a constant string that contains the command to be passed through to the linked server. If the N is included, the string is interpreted as nvarchar data type.

And I am guessing the section N'[Uncategorized (Local)] goes and gets the name of the sysadmin and passes it to execute the job as the sysadmin, but I am not finding a good reference that indicates how all three segments are working together.

Best Answer

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC.

See the sp_add_category documentation:

[ @name = ] 'name'

The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

[Uncategorized (Local)] just indicates a default category when you didn't specify a category on the SQL Agent Job. The leading N just indicates the string is nvarchar and not varchar.

Please read through Organize Jobs to learn more about job categories

The code you are looking at is most likely just a scripted out job. For example when I right-click a random job on the SQL Server I'm looking at and script job as > create > new query editor window this is the result:

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Warmup_SSRS_EP', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=3, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Warmup_SSRS_EP', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'domain\user', 
        @notify_email_operator_name=N'monitor operator', @job_id = @jobId OUTPUT

Where the creation of the category is just included in the script to make sure it exists because otherwise the following procedures would error out.

Related Solutions

Sql-server – How to script out push subscription creation at the subscriber

I'm going to outline a T-SQL way that uses most of what you already have.

Create a table to hold your publication information (for the solution that I have written, it's just the name of the publication and an identity column)
Create a table to hold your subscriber information (in mine, I have name of subscriber, subscriber database, identity column, and a column that references back to the publication)
When you want to create a new subscription, you put a new row in the subscriber table with the relevant information
In your "add subscription" script, do a left join between your table and syssubscriptions to find out what subscriptions are in your table but don't yet exist. Run a cursor over that info and for each subscription that doesn't exist, create it.
After you're done adding all of the subscriptions, call sp_startpublication_snapshot for each publication that has uninitialized subscribers.
Fin (aka "you're done").

Grant Sysadmin Permissions to ‘NT AUTHORITY\SYSTEM’ – SQL Server

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

Best Answer

Related Solutions

Sql-server – How to script out push subscription creation at the subscriber

Grant Sysadmin Permissions to ‘NT AUTHORITY\SYSTEM’ – SQL Server

Related Question