MS SQL server 2015 Linked server to ASA 17

[former_SQLA_member1694914]

Hello, I need to create linked server on MS SQL srv 2015 to ASA 17. I installed SQLANY client on machine where MS SQL server is, created ODBC profile which nicely connect to ASA. I would like that Linked server get access just to one view in ASA and nothing else. I created user in ASA for this purpose and give him only View Privileges to selected view. In linked server user can connect but could not see view and on ASA server console there is error "Login procedure 'sp_login_environment' caused SQLSTATE '52W09' login procedure sp_login_environment not found" I assume that user doesn't have enough rights to view this view. The same error I get when I connect with ISQL and here I also could not select my view. What Privileges or Roles I have to give to user that he could access and run this view? If I run view as dba, I have no problem.

Thanks for any help! Tomaz

[chris_keating]

I have not tested this on MSS with a linked server. This resolves the issue in dbisql (this user configuration will not be able to connect using SQLCentral). Adding the user 'keating' with limited

  CREATE USER "keating" IDENTIFIED BY 'sql';
  REVOKE EXERCISE OPTION FOR ROLE "PUBLIC" FROM "keating";
  SET OPTION "keating"."login_procedure" = 
     'dbo.sp_login_environment';
  GRANT EXECUTE ON "dbo"."sp_login_environment" TO "keating";

[chris_keating]

The user should be granted the role PUBLIC.

[former_SQLA_member1694914]

Thanks Chris, I gave the user the role PUBLIC. He now connects without problem and I see he could select just from view which I gave him privileges. Is it possible that he doesn't see list of all tables, views...? Regards Tomaž