SQL 2005 Security to access 2 databases on same server via Stored Procedure

15 pts.
Tags:
Database management
Security
SQL 2005
SQL Server 2005
Windows Server 2003
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

Setup both databases with cross database chaining.

ALTER DATABASE FirstDB
SET DB_CHAINING ON
GO
ALTER DATABASE SecondDB
SET DB_CHAINING ON
GO

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.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • A000016
    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
    15 pointsBadges:
    report
  • A000016
    [...] SQL 2005 Security to access 2 databases on same server via Stored Procedure asked by A000016 and answered by [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following