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

asked 18 Dec '21, 12:21

Tomaz's gravatar image

Tomaz
1013410
accept rate: 0%


The user should be granted the role PUBLIC.

permanent link

answered 18 Dec '21, 21:10

Chris%20Keating's gravatar image

Chris Keating
6.4k39107
accept rate: 30%

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";
permanent link

answered 19 Dec '21, 11:20

Chris%20Keating's gravatar image

Chris Keating
6.4k39107
accept rate: 30%

edited 20 Dec '21, 19:42

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×31
×2

question asked: 18 Dec '21, 12:21

question was seen: 151 times

last updated: 20 Dec '21, 19:42