I am trying to establish a connection between two SQL severs located in different location and trying to read/fetch the tables. When I connect the remote Standard SQL Server from SQL Server Express and fetch table, it works fine. When I try to connect SQL Server Express from Standard SQL Server, I get an error Msg. Just to mention that I do have the writes to read, create, update tables in the Standard SQL Server.

Error Msg is:

Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 34 [Batch Start Line 2] User does not have permission to perform this action.

Remote SQL Server is "DKLYDB3" and Local SQL Server is "PC89450\SQLEXPRESS"

Query which works fine is below.

exec sp_addlinkedserver  @server='DKLYDB3';
FROM [DKLYDB3].BRISK_AIS.dbo.Crossline;

Query which does not work is here.

exec sp_addlinkedserver  @server='PC89450\SQLEXPRESS';
FROM [PC89450\SQLEXPRESS].BRISK_AIS.dbo.Crossline;
  • 2
    do you have privileges to execute the sp_addlinkedserver on the DKLYDB3 instance? you need ALTER ANY LINKED SERVER permission to execute it.
    – Ronaldo
    Oct 11 at 15:58
  • I do have the writes to read... That does not mean you have the appropriate permissions to add a linked server. Now would be a good time to have a discussion with the group responsible for managing your systems. Adding a linked server is something that needs to be documented and needs to be added to the disaster recovery plan.
    – SMor
    Oct 11 at 17:42
  • Hi both: Today I discuss this issue with our IT section. They are not yet able to figure that part out. Then I myself tried to compare the privileges I have in local SQL Express edition with the Standard SQL remote server. In the local Express version, under Object Explorer, under Security, in Login properties (use mapping), I am marked as db_owner and under Server Role marked as sysadmin. May be these two additional privileges are the reason for success in Local DB Server. In the Standard SQL server I added myself as db_owner but still not work. I am not able to change sysadmin myself.
    – Riz
    Oct 11 at 18:56
  • Tomorrow I will ask IT section to change add myself for sysadmin. I do not know if this will work. I do not know either if they allow me to have this privilege. Apart from these two things I mentioned, I do not know if I have any other privilege in Local SQL server which I do not have in Standard SQL remote server. Everything else looks same.
    – Riz
    Oct 11 at 19:00
  • As a sysadmin you'll definitely be able to create it. A sysadmin can do anything on a SQL Server instance, it's the highest privilege level a user can be. But it might be way too much.
    – Ronaldo
    Oct 12 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.