July 17, 2021 at 4:06 am
I have two databases: Db1 and Db2. For security (and other) reasons I don't allow the database users direct access to the tables or views so all SELECTs, INSERTs, etc are inside stored procedures. All works fine except when a stored procedure in Db1 needs to access data in DB2. I have to grant the user SELECT (or other) permission to the table in Db2 that is being accessed by the stored procedure from Db1 - which basically defeats the whole "do not allow direct access" stuff.
Is there a way to create roles, schemas, anything to allow a stored procedure from one database full access to another database in such a way that the user does not get the direct access to the tables?
Thanks
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
July 17, 2021 at 7:43 am
Yes, it is possible. There are three different ways to go:
While the first is the simplest, it also opens for a security risk, as its permits users in the database who permissions to create users and procedures to elevate to get sysadmin rights.
As for the other two. I am not going to explain them here, but refer you to my article Packaging Permissions in Stored Procedures where I describe all this detail. Unfortunately, it is a quite a long article, and the chapter on cross-database access is at the end. But once you have gotten through it, you should be able to make an informed decision.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 18, 2021 at 4:04 am
Did not have time to fully digest the entire article, but the pieces I managed to read were great. Thanks a lot. Will have to experiment a bit since the procedures in Db1 are in a different schema (Web) than the tables in Db1 and Db2 (both dbo).
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
July 21, 2021 at 10:20 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply