none
Permission for Linked Server User

    Question

  • Dear,

     

    What minimum permission do i need to grant to a Remote Login to  create a linked server.

     

    Regards

    Sufian

    Thursday, August 30, 2007 4:17 PM

All replies

  • I think you will need to use the sp_addlinkedsrvlogin proc to add the login to the linked server.

     

    The minimum permissions for this is alter any login in SQL 2005. 

     

    To add the linked Server use the sp_addlinkedserver proc.

     

    The minimum permission for this proc is alter any linked server

     

    HTH,

     

    -Steven Gott

    SDE/T

    SQL Server

    Thursday, August 30, 2007 4:30 PM
    Moderator
  • I have created a User say XXX with default database <master> with no other permission apart  from Connect.

     

    What other permission do i need to grant to user so that i can use that user as a remote Login in linked Server.

     

    regards

    sufian

     

    Thursday, August 30, 2007 8:12 PM
  • I am pretty sure you need to grant alter any login and alter any linked server to the login for the user XXX

     

    I did a quick search and found a related article that may help

     

    http://www.databasejournal.com/features/mssql/article.php/3691721

     

    HTH,

     

    -Steven Gott

    SDE/T

    SQL Server

    Saturday, September 01, 2007 1:18 AM
    Moderator
  • Hi Steven,

     

    Thanks for the link it helped in better understanding of Linked Server.

     

    My question is still pending regarding the permission to user used in making connection to the remote server?

     

    Well i got the resolution.

     

    We have to grant only two permission to the user to use it in linked server creation.

     

    GRANT CONTROL SERVER TO LinkedServer_User;

    GRANT ALTER ANY LINKED SERVER to LinkedServer_User;

     

     

    regards

    Sufian

    Saturday, September 01, 2007 10:27 AM
  • Sufian,

     

    The CONTROL SERVER permission implies ALTER ANY LINKED SERVER so if you really need CONTROL SERVER that permission alone should suffice.

     

    I am surprised that this doesn't work:

     

    GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

    GRANT ALTER ANY LOGIN TO LinkedServer_User;

     

    Can you confirm that this doesn't work?  If it doesn't then I can open a bug with the product group.

     

    Thanks,

     

    -Steven Gott

    SDE/T

    SQL Server

    Saturday, September 01, 2007 1:22 PM
    Moderator
  •  

    Created a new user abc and given readonly access to one database x only.

     

    No other permission a part from DataReader to abc.

     

    Created new linked server giving the username and password in the (Be made using the security context) option.

     

    Linked server created successfully.

     

    and i can only see the database on which user have read only access.

     

    After granting below mentioed permission no effect on cataloge

     

    GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

    GRANT ALTER ANY LOGIN TO LinkedServer_User;

     

    Well i think i need to give access to user on other databases also.

     

    Please open  a bug if it is.

     

    Regards

    Sufian

     

     

     

     

    Monday, September 03, 2007 8:49 PM
  • hi,

     

    although this is a bit older conversation i've run into it when looking for the best solution to give a user linked server permissions. I don't think that the user should gain full server control and found exactly that what Steven allready mentioned:

     

    The minimum rights needed to be able to link to a server and have access to it is:

     

    GRANT ALTER ANY LINKED SERVER TO LinkedServer_User;

    GRANT ALTER ANY LOGIN TO LinkedServer_User;

     

    When you want to have access to excel, you will need to map with the excel user, being 'admin' like this:

    EXEC master.dbo.sp_addlinkedsrvlogin @server,'False',NULL,'Admin',''

     

    When you want to have access to the linked server, you can map the user if there is an equal user in the other database, or you can map everyone to the remote user as follows:

     

    EXEC master.dbo.sp_addlinkedsrvlogin @server, 'False', NULL, @rmtuser, @rmtpassword

     

    We did use the latter since our (old) VB6 application did not get access, since no user could be found in this second hop configuration. Therefore we set the security to the linked server in such way that any user will connect with the same security context

     

    This worked for us!

    Saturday, June 28, 2008 10:27 AM