January 10, 2003 at 10:06 am
I have a stored procedure that access data in another database. I have a role that can execute the stored procedure. I’m trying to avoid having to create two roles, and assign all the users to both roles. Is this possible?.... Thanks
January 10, 2003 at 10:36 am
January 10, 2003 at 10:58 am
Maybe? If both db's are owned by same owner, you could create a view to your data in the main db, give the roles access to it.
Andy
January 13, 2003 at 7:41 am
If you have the "guest" user account set up in the "data" database then the permissions tied to it would flow through to the logins in the "SP" database that don't exist in the "data" database. If the stored proc is only selecting records in the "data" db then the ability to execute the SP should be enough. If it is manipulating data then the guest user would have to be granted those privileges. NOT something I would suggest.
January 13, 2003 at 1:33 pm
SQL Server 2000 SP3 is supposed to have an option to enable cross-database ownership chains. This might help you out (in 1-2 weeks).
Bryant E. Byrd, MCDBA
SQL Server DBA/Systems Engineer
Intellithought, Inc.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
January 14, 2003 at 1:23 am
There was an earlier post about this.
There should be ownership chains in SQL 2000 SP2 (maybe before that too). The problem in that situation was the fact that the dbo user in the two databases did not have the same SID in the sysusers table. Maybe something to check.
This still means that you have to define a role in each database. Execute on the procedures and sufficient rights on the database containing the data for the owner of the SP.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply