Solved

limitations of linkserver

Posted on 2013-01-29
3
113 Views
Last Modified: 2013-01-30
There is a stored proc in DB1 (SQL 2000) that deletes and inserts into a table in another database (DB2) in the same server.

But now the other database (DB2) had to be moved to another SQL 2008 server and henee linked server was created.

but now the sp fails.. because LS only reads.. no other permissions..

i thoughts about 'execute as', but that is not available in sql 2000..

what would recommend as the best practise to just give just enough permission to be able to do the del/insert into the other database.
0
Comment
Question by:25112
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 38831655
Seems like a permission issue to me, you can try creating the linked server using a the SQL login which has permission to delete on SQL 2008 Server.
0
 
LVL 5

Author Comment

by:25112
ID: 38832162
thanks.. i find that the linked server login did not have those permission..

now, i have a touble with this solution..

the linked server (destination) DB2 is being linked to many servers including DB1.. all the servers use the same readonly login (DataConnect_Login) to the DB2 database. Only this proc in one app needs more than read..

do you have any suggestion to keep this clean (in the above scenario)..

If DataConnect_Login is given update and delete on this login for this proc, then all servers have update/del permission on this table.. rather only DB1 needs this feature. (the idea is not to give DataConnect_Login more than read, but also keep it simple.. I am open to ideas.. if there is no other way, then will revert to the first option)

Is there any out of the box idea  beside the above?

thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38835832
>do you have any suggestion to keep this clean (in the above scenario)..
SPN will be your best shot, if you setup the SPNs you dont have to specify explicit SQL logins
Google for SQL SPN, its pretty simple to setup
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

13 Experts available now in Live!

Get 1:1 Help Now