In SQL Server, users can only access the specified stored procedure.

In SQL Server, users can only access the specified stored procedure.
In addition to ticket sales at the station, there is a passenger ticket sales system that allows external units to sell tickets through Internet or leased lines. To this end, we have created several necessary storage processes as interfaces, used by the other party to develop consignment software and create a new login name for the other party to connect to the database
Now, the problem arises. The storage process we provide is complicated (the business itself is complicated), which requires access to a large number of tables, stored procedures, functions, etc, we only want the other party to use these stored procedures, rather than the user to access other information such as tables. First, we want the other party not to see the data that should not be viewed. Second, we want the other party to use these stored procedures for security, in the event that the other party accidentally makes a Delete request, the other party may be crying. The third is to prevent the other party from directly extracting data from the table by simplifying the query (the data volume in the table is large, there is an index, and the other party may ignore it ), this results in low server efficiency.
To simplify the description, we assume that the storage process is myproc1, the login name is myuser, and the content of myproc1 is select * From Table1.
Set myuser to execute only myproc, log on with myuser, and execute the Stored Procedure myproc1: exec [DBO]. [myproc1], rejected by the server, prompting that you do not have the permission to query table 1. If you are granted the query permission, the problem mentioned above cannot be solved, how can I only allow myuser to execute myproc1 and not allow it to access Table1? After the experiment, I used the linked server to better solve the problem. The method is as follows:
1. Create a linked server. Note that you are connecting yourself to the server. For security reasons, I have modified the default port of SQL Server and the new port number is 4000. Therefore, the linked server name is "server name, 4000", and the login name is Sa. Note that the login name is Sa or other accounts that can access relevant data
2. Create a New stored procedure, for example, myproc2, and allow myuser to execute the Stored Procedure
3. The content of myproc2 is exec [server name, 4000]. [database name]. DBO. myproc1

Now, you can use myProc2 as an external interface, that is, you can execute myProc2, such as EXEC [dbo]. [myProc2]. Let's take a look at the execution process. After you log on with myUser, execute myProc2 and myProc2 and then call myProc1 on the linked server. Because the linked server is used, the account on the linked server is changed to sa, which indirectly prevents users from querying other tables.