SQL 2005 Security to access 2 databases on same server via Stored Procedure
We are tyring to apply best security practices by only granting EXECUTE security to Stored Procedures connected to a Role and make users members of the role. However the SP needs to access data from another DB on the same server and when trying we receive "SELECT permission was denied on the object". We have created the same user(s) on the 2nd database and granted PUBLIC access to the DB - but do not wish to grant select access to the specific tables trying to be accessed. We have successfully created a SP on the 2nd DP which is called from the first SP on the originating DB and data can be accessed - However this doesn't seem like good coding to have multiple stored procedures in 2 different DBs on the same server. Does anyone know of a better way to provide security that would allow a single SP to access data from 2 DBs on the same server without having to grant the role specific security rights to the needed table in the 2nd DB ? Greatly apprecaited -
Software/Hardware used:
Windows 2003 Standard / SQL 2005 Standard
Software/Hardware used:
Windows 2003 Standard / SQL 2005 Standard
ASKED:
April 15, 2010 8:33 PM
UPDATED:
April 16, 2010 8:18 PM
Answer Wiki:
Setup both databases with cross database chaining.
<pre>ALTER DATABASE FirstDB
SET DB_CHAINING ON
GO
ALTER DATABASE SecondDB
SET DB_CHAINING ON
GO
</pre>
After you do this the users will need to exist in both databases. With the stored procedure in the FirstDB database, no rights need to be granted to the objects in the SecondDB database.
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you Mrdenny for the great information.
After much research on the topic of DB_CHAINING – it appears this is definitely the route to take.
Just knew there had to be somethign like this.
When you can link DB servers – there certainly should be a way to link DBs and this fit the bill perfectly.
Again thanx