Post reply

User permission

  • August 1, 2019 at 3:05 pm

    #3667932

    I have an user that have read permissions to two databases in another server, and I would like them to use a linked server to read the information and not connect directly to that server.

    I am not sure how can I block them from connecting to the server but yet being able to read the databases I granted permission to, using the linked server.

     

    thanks

    astrid

  • August 1, 2019 at 3:39 pm

    #3667944

    The users don't need direct access to the remote server in order to connect to it through a linked server... depending on how the linked server is configured.  Create a login on the remote server that has only the permissions that the users need, and use that as the login credential when you create your linked server object.

    John

  • August 1, 2019 at 3:43 pm

    #3667949

    Thanks for your reply.

    I didnt mean that he is accessing the server itself.

    I mean I want him on the SSMS to log and do his queries on my DEV enviroment, but if there are tables he doesnt have, he can use the linked server to my TEST  enviroment, meaning select * from TEST.MYDB.dbo.mytable

    I just dont want him to be able to log in to TEST through the SSMS directly.

  • August 1, 2019 at 3:47 pm

    #3667950

    Indeed.  Make sure he doesn't have access to TEST, if you don't want him connecting directly.  Then, create a login on TEST with the permissions he needs.  He doesn't need to know any of the details of that login.  Set up the linked server on DEV to point to TEST using that login.

    John

  • August 1, 2019 at 4:43 pm

    #3667972

    Thanks but I am not sure what you mean.

    it is a domain user, what details are we talking about?

  • August 1, 2019 at 5:15 pm

    #3667985

    The presumption is that your user is selecting from a linked server connection to the 2nd server.  But, from re-reading your first post, it sounds like they're connecting directly to the server that has the databases on it, that you only have one server.

    If that's the case, you're not going to be able to give them access to the data without either allowing them to connect to the SQL instance via SSMS or such, or standing up a SQL instance for them to connect to which then has a linked server connection to the first instance (and the linked connection is restricted to what it can query,) or going whole-hog and writing a front-end for them to use.

    Frankly, the last two of those options would be a fair amount of work.

    What you can do, and I'm presuming you want them to only have read access to the data, not write / update / delete / execute, would be to create a role in each of the databases which has ONLY select permissions on the required table(s), then add their existing domain login to those roles and remove it from any other database roles.  At the server level, they would only belong to the Public role, and no others.

  • August 1, 2019 at 6:51 pm

    #3668019

    Hi,

    thanks for your reply.

    I have given them only read access, so that permission to only read a specific database is not the problem.

    have a great day!

    astrid

  • August 1, 2019 at 7:25 pm

    #3668030

    Setup the linked server security so that it does not use that users security context. Map the users to some other account that has access to the server/database and select "not be made" for other logins not in the list. Then you can remove the access to the server from the logins you don't want accessing it directly but leave the access for the other account you set up for the linked server. Those users would then access the other databases through the linked server account for the linked server and wouldn't be able to access the server directly.

    Sue

     

  • August 1, 2019 at 7:28 pm

    #3668033

    thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply