I would like to have 2 db (Dev & Prod). CarsDevDB & CarsDB. I have 2 users, CarsDevUser (Allow Remote Access, Port 5000) and CarsAdmin (only allow local connection).

I would like to connect to CarsDevDB with the user CarsDevUser from anywhere REMOTELY using (IP):5000.

However, I would like to connect to CarsDB using ONLY LOCAL connection. CarsDB/CarsAdmin should NOT have any remote connection whatsoever.

CarsDevUser will not access to any other database.

How can I proceed with this? I've enabled Remote connection and got it to work, I can't limit it to one database yet though.

  • 2
    Can't you just only grant explicit access to this one database? What server role(s) is CarsDevUser in? – Aaron Bertrand Feb 27 '17 at 21:07
  • What Aaron said plus just make CarsAdmin a local windows account and create the login from that. – Sean Gallardy Feb 28 '17 at 0:51
  • How would I do that? If possible, I wouldn't mind turning off the remote connection but give only THIS database the remote access (if possible). CarDevUser current only has "public" server role. What else should I add? I guess I should just make the prod DB "Window Account Only" access and the other dev db SQL authentication? – AAA Feb 28 '17 at 4:34
  • Combination Server Roles and Schema access can make this happen, please hire a senior DBA, to help with that :) – MarmiK Feb 28 '17 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.