This topic describes how to create a SQL Server Agent job in SQL Server 2012 by using SQL Server Management Studio, Transact-SQL, or SQL Server Management Objects (SMO).
Limitations and Restrictions
To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role.
Assigning a job to another login does not guarantee that the new owner has sufficient permission to run the job successfully.
Local jobs are cached by the local SQL Server Agent. Therefore, any modifications implicitly force SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is more efficient to call sp_add_jobserver last.
You must be a system administrator to change the owner of a job.
For security reasons, only the job owner or a member of the sysadmin role can change the definition of the job. Only members of the sysadmin fixed server role can assign job ownership to other users, and they can run any job, regardless of the job owner.
If you change job ownership to a user who is not a member of the sysadmin fixed server role, and the job is executing job steps that require proxy accounts (for example, SSIS package execution), make sure that the user has access to that proxy account or else the job will fail.
*Notes; Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.
Adding a linked Server using the GUI
To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.
In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) > Add New Linked Server
The “New Linked Server” Dialog appears. (see below).
For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name)
Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
Provider – Select “Microsoft OLE DB Provider for SQL Server”
Product Name – type: SQLSERVER (with no spaces)
Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
ProviderString – Blank
Catalog – Optional (If entered use the default database you will be using)
Define the Linked Server Security
Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made. Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server. If instance is on the same server and the logins have the appropriate permissions, I recommend this one.