[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

Create Read only LinkServer User access

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?

GRANT ALTER ANY LOGIN TO USER

GRANT ALTER ANY LINKED SERVER TO MDVBO_READONLY
0
pengbsam
Asked:
pengbsam
  • 2
  • 2
1 Solution
 
mayank_joshiCommented:
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 srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LinkServer1')EXEC master.dbo.sp_dropserver @server=N'LinkServer1', @droplogins='droplogins'
GO

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='########'

GO

Open in new window

0
 
pengbsamAuthor Commented:
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.
0
 
mayank_joshiCommented:
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.







0
 
pengbsamAuthor Commented:
Didn't fix my problem
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now