SQL Server Linked Server creation require sysadmin Role
While creating new linked server on SQL Server 2012 using Microsoft SQL Server Management Studio, I got the following error message indicating that the user who add linked server must be a member of SQL Server sysdamin server role
Microsoft SQL Server Management Studio
A required operation could not be completed. You must be a member of the sysadmin role to perform this operation. (SqlManagerUI)
The cause of this error is related with the user who is trying to create linked server should have the sysadmin server role. If the user who is performing linked server creation does not have sysadmin role, then the above SQL Server Management Studio will appear. When required permissions are granted to the user, to create linked server on SQL Server is a straight-forward task.
In order to grant sysadmin server role to the related SQL login, connect to SQL Server 2012 database instance first.
Then drill-through Security > Logins.
Find the related SQL login user, then open Properties screen by double click on the sql login. Or you can choose "Properties" from the context menu displayed by right-click on the sql login username.
Go to Server Roles page, using the left menu of the Login Properties dialog screen.
Be sure that sysadmin server is marked and granted to the sql login.
Below is the technical details of the above SQL Server linked server creation error message:
Program Location:
at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator. AllocateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider, CDataContainer dc)
at Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator. Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument initializationXml, IServiceProvider dialogServiceProvider)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument doc, IServiceProvider provider, ISqlControlCollection control)
at Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument doc, IServiceProvider provider)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolMenuItemHelper. OnCreateAndShowForm(IServiceProvider sp, XmlDocument doc)
at Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl. ThreadStarter.StartThread()
A required operation could not be completed. You must be a member of the sysadmin role to perform this operation. (SqlManagerUI)
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties.InitializeUI( CDataContainer dataContainer)
at Microsoft.SqlServer.Management.SqlManagerUI.LinkedServerProperties..ctor( CDataContainer dataContainer)