June 10, 2020 at 3:29 pm
I can able to grant user who is non sysadmin to linked server by granting rights to user on SYS.XP_PROP_OLEDB_PROVIDER. However, the caveat is it allows user to access all the linked server on the server. Is there a way i can Just grant specific linked server? Please advise?
June 11, 2020 at 3:39 pm
I think the only way to limit this is to put restrictions on the linked server itself.
What I mean is to set the authentication on the linked server to be the calling users credentials. This way if user A has permissions on the server the linked server points to, they can run their SELECT's. If they don't, they will get errors.
Basically, the permissions are set up on the remote SQL Instances that the linked servers point to, not on the server containing the linked server.
At least that is how I've always done linked servers. Permissions on the destination side, not the source side.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 11, 2020 at 5:52 pm
I can able to grant user who is non sysadmin to linked server by granting rights to user on SYS.XP_PROP_OLEDB_PROVIDER. However, the caveat is it allows user to access all the linked server on the server. Is there a way i can Just grant specific linked server? Please advise?
What permissions are you trying to grant for what actions? You haven't specified that. If you had an error with that particular extended stored procedure, it usually happens when browsing the catalogs for the linked server. You may want to consider having the users execute sp_catalogs instead of using SSMS to browse the linked server catalogs. If they have no idea what they are querying, you can have other issues with their querying that affect everyone who uses that instance.
Sue
June 11, 2020 at 7:28 pm
Thanks Brian and Sue! Linked server is created using the security context. I am trying to grant select access to the account which can be used as part of the Job. Since the requester has no idea what level of access is needed for the account. So I have to grant rights to extended store proc in master SYS.XP_PROP_OLEDB_PROVIDER. I believe this would give him access to the linked server and what ever account is used under the security context will control the rights right?
June 11, 2020 at 9:21 pm
A linked server to be used as part of a job wouldn't have anything to do with xp_prop_oledb_provider. As I said, that extended stored procedure would be needed to browse the catalogs using SSMS. I don't think a job would browse the catalogs with SSMS. The access for whatever account is accessing the linked server is based on the security configuration of the linked server as Brian explained above but you don't really create one "using the security context". Using which security context? There are two different options that use that phrase. And if they don't know what access they need, that really means they don't need access. If they can identify what views, tables, functions they are going to select from, you can work on setting something up at that time. I have no idea why you would have started by granting execute permissions to xp_prop_oledb_provider unless you just read about it and just granted the permissions. Scary thought.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply