SQL Server permissions for SP_LINKEDSERVER

0
votes

Background: I have a 3rd party developer for whom we have created a login into our SQL Server with the minimal permissions required for them to do their work. We gave them "deny datareader" and "deny datawriter", though we granted them execute permissions on the two stored procedures which they require.

Issue: It was brought to my attention that certain holes in our setup will be vulnerable to this new user, should they want to act maliciously. We have access from this server to various other SQL Servers via linked servers. The logins to the remote servers aren't using the users login (login as self is false), rather, we have linked server logins. Hence, should they come across the names of the linked servers they'd be able to have unfettered access to the linked servers. I did revoke access to "sp_linkedservers" to the role "public" in order to prevent them from seeing the list.

Question: Until we rectify the situation with the linked servers, is there a way for us to patch this hole? Something like denying access to linked servers would be nice, though from what I understand it would be impossible... any help would be greatly appreciated!

Disclaimer: While I don't suspect this user of malicious behavior, I do want to prevent the possibility, while at the same time learning another part of SQL Server permissions.

Thanks so much!!

~Eli

1
sql-serverpermissions
I deal with 3rd party developers all the time. They don't need execute permissions. The stored procedures should be set up as EXECUTE as OWNER. Then give them ONLY datareader and datawriter rights to the database or specific tables. You might also need to grant them View Definition if they need to read the schema. That locks down the system pretty good. – JVC
This sounds to me like an issue with the configuration of the linked server security (as opposed to the security for the login for the 3rd party developer). Can you look at the properties of one of the linked servers and post a screen shot of the "Security" page? Blur out any sensitive information. – Dave Mason
@JVC - I was more careful than that. I gave them denydatareader and denydatawriter, with explicit permission to execute the two SP's which are required. This locks down everything on our side, aside for the linked servers. – Eli
@DMason - That is the case, and this question was to address a temporary fix until we can iron out what else is relying on these linked server logins. The issue with the linked server is the fact that they were given a proxy login to the linked server (thankfully whoever set it up had the sense to NOT give it the SA credentials) – Eli

1 Answers

0
votes

I asked around some more, and forwarded this question to others on twitter until I was guided to the correct location. See attached screenshotenter image description here