Create Read only LinkServer User access

Posted on 2011-05-01
Last Modified: 2012-05-11
This is regarding a permission issue, I recently created a linked server with permission SA. After a few week, before moving to production I would like to make the user a read only user, in case someone trying to overwrite the data from this link server access.

Server is MS SQL2008 R2.

When I try a user (this is the user that is on the linked server). I am getting error:15007, "USER" is not a valid login or you do not have permission.

I have already given the user sysadmin, on user mapping .I have given db_datareader for all the database it is trying to access.

Per another suggestion Did below. Still getting the same error message. Any ideas?


Question by:pengbsam
    LVL 9

    Expert Comment

    check the linked server name by running:-

    select * from sys.servers

    Open in new window

    drop the linked server ( say LinkServer1)  using:-

    IF  EXISTS (SELECT FROM sys.servers srv WHERE srv.server_id != 0 AND = N'LinkServer1')EXEC master.dbo.sp_dropserver @server=N'LinkServer1', @droplogins='droplogins'

    Open in new window

    create a read only user on the server (say user1).Then recreate the linked server using:-

    EXEC master.dbo.sp_addlinkedserver @server = N'LinkServer1', @provider=N'SQLNCLI', @datasrc=N'ServerInstanceName', @catalog=N'DatabaseName'
     /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkServer1',@useself=N'False',@locallogin=N'sa',@rmtuser=N'user1',@rmtpassword='########'

    Open in new window


    Author Comment

    And it still give me the same error message. I don't know if it is any different doing via code vs doing via the wizard.

    I think it has something to do with my read only user permission on the source server.
    LVL 9

    Accepted Solution

    i think the read only user has not been created properly.

    try dropping and then recreating the user.
    for read only user let the server role be public(default) only
    and check user mapping as db_datareader (no need of sysadmin) for each database it needs to access.

    then drop and recreate the linked server.


    Author Closing Comment

    Didn't fix my problem

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now