I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, obviously I won't be using that going forwards however I just wanted to make sure the procedure worked and SA having all privileges would cater for this.
Again for troubleshooting purposes, I initially tried SA using the 'be made using this security context' option which worked well but obviously is not secure as I want to reduce who has access to the linked server.
When I tried the 'not be made' option with a local and remote login I receive an error suggesting that no login mapping exists although the login worked when I used the 'be made using this security context' option. What am I doing wrong?
Best Answer
I think you need to check the
Impersonate
option or not usesa
for testing—use another SQL Login instead for testing this functionality. I'm not 100% certain it's required but you may also need to consider ensuring the remote logins have a matchingsid
on both SQL Server instances as I wrote about in the User mapping to a standby database post if you do not use impersonation so something easy enough to test usingsp_helplogins
andCREATE Login
if needed.Why
I don't recall where I heard or read about it, or if it's even applicable in this case, but I thought the
impersonate
rights were inherited implicitly for logins withsysadmin
and localdb_owner
rights.Impersonation
If you go with using impersonation, below are a couple syntax examples to use for domain user account logins and local SQL logins which connected to the
master
DB.