user without login & linked server

Mudit Gupta 81 Reputation points
2022-04-01T18:10:51.37+00:00

Hello

I have one server where I am using [user without login , svc_proc] which has privileges to execute stored procedure. End users are running EXECUTE AS user='svc_proc' to run stored procedure. It works fine when stored procedures are hitting against same host server.
But some of those stored procedures are querying other SQL Servers through Linked servers.
Since under the stored procedure, svc_proc user is executing it, how can I map a this user to remote SQL Server?

My guess is It cannot be mapped, because svc_proc has no login & linkedserver needs a login to map to.

I fixed it by using *Be made using this security context & I enter a remote login & password ( which only has read rights on the remote DB).

Is this way secure or is there any other method to make it work?
Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,122 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-04-01T22:05:40.763+00:00

    To start with, normally you are sandboxed inside the database when you use EXECUTE AS USER, and you cannot access things outside the database. This include linked servers, since they are server objects.

    Now, you say that you can actually access the linked server and you also say "same host server". This indicates that the database is marked as trustworthy and the database owner holds the permission AUTHENTICATE SERVER. Which will be the case if the owner is sa.

    Beware that this is potentially a security issue. It is OK if anyone who has elevated permission in the database already is sysadmin. But if there is a user who is member of db_owner and who should have no business on server level, this user can exploit this situation to sysadmin.

    As for the linked server, yes, that would be difficult. It would be better if users used EXECUTE AS LOGIN instead. (I assume that they don't run this command directly, but there is an application that issues the command.) That would permit you to set up an explicit login mapping, and it would remove the need for having the database marked as trustworthy.


1 additional answer

Sort by: Most helpful
  1. YufeiShao-msft 7,076 Reputation points
    2022-04-04T08:44:24.253+00:00

    Hi @Mudit Gupta ,

    It is true that you cannot map if use user without login.
    If you definitely want to use an option to connect without a mapped login, 'Be made using the login’s current security context' is the best option

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments