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]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy