I created a linked server to another SQL Server and to limit access to the Linked Server, I used Local Server Login mapping to Remote Server mapping option and for the login not listed in the mapping, I picked "Be made without using a security context" option.
When I do this as sa, since even sa is not allowed to use the linked server, I get an error at the end of creation wizard. I accept the error and continue.
The following error message is generated if you go back to update the properties and save.
But this is an expected behavior. Now if you connect to the SQL Server using the login mapped in the previous step, and try to browse the linked server catalog, you will get this error
The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)
Howver, notice that you can run a query against the linked server from Management Studio. The error message only appears if you try to browse the catalog. If your users are only querying the linked server using T-SQL then you can ignore this error, otherwise you can grant access to mater database to the user and additionally grant execute on the procedure mentioned in the the error to the user.
USE master
GO
CREATE USER youruser TO LOGIN youruser
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO youruser
When I do this as sa, since even sa is not allowed to use the linked server, I get an error at the end of creation wizard. I accept the error and continue.
The following error message is generated if you go back to update the properties and save.
But this is an expected behavior. Now if you connect to the SQL Server using the login mapped in the previous step, and try to browse the linked server catalog, you will get this error
The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’, schema ‘sys’. (Microsoft SQL Server, Error: 229)
Howver, notice that you can run a query against the linked server from Management Studio. The error message only appears if you try to browse the catalog. If your users are only querying the linked server using T-SQL then you can ignore this error, otherwise you can grant access to mater database to the user and additionally grant execute on the procedure mentioned in the the error to the user.
USE master
GO
CREATE USER youruser TO LOGIN youruser
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO youruser