|
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 |
|
The user should be granted the role PUBLIC. 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ž
(19 Dec '21, 05:49)
Tomaz
|
|
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";
|