nd DB2 say.
In DB2 I have assiged EXEC Permissions for a user on a Stored Proc that chec
ks against a table in DB1 (e.g.)
--In DB2 Stored proc
SELECT * FROM Customer
WHERE CustomerID IN (SELECT CustomerID FROM DB1.dbo.OtherCustomers)
However the user gets an error stating that they do not have select permissi
ons on the other table (Othercustomers in db1)
Is there any way I can assign permissions just on this stored procedure (in
db2) without giving select permissions
for the user in DB1 ?
Thanks
GerryYour objects need to be owned by the same login in order to maintain an
unbroken ownership chain so that users don't need permissions on indirectly
referenced objects. You can map the 'dbo' user to the same login in both
databases by specifying the same database owner. For example:
USE DB1
EXEC sp_changedbowner 'MyLogin'
GO
USE DB2
EXEC sp_changedbowner 'MyLogin'
GO
Additionally, you need to enable cross-database chaining in both databases.
This option was introduced in SQL 2000 SP3. For example:
EXEC sp_dboption 'DB1', 'db chaining', true
EXEC sp_dboption 'DB2', 'db chaining', true
See the cross-database ownership chaining in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0108492C-77C3-4694-9F2E-C824675D7B03@.microsoft.com...
quote:
> I have SQL2000 with a couple of databases on it (same instance) - e.g. DB1
and DB2 say.
quote:
> In DB2 I have assiged EXEC Permissions for a user on a Stored Proc that
checks against a table in DB1 (e.g.)
quote:
> --In DB2 Stored proc
> SELECT * FROM Customer
> WHERE CustomerID IN (SELECT CustomerID FROM DB1.dbo.OtherCustomers)
> However the user gets an error stating that they do not have select
permissions on the other table (Othercustomers in db1)
quote:
> Is there any way I can assign permissions just on this stored procedure
(in db2) without giving select permissions
quote:sql
> for the user in DB1 ?
> Thanks
> Gerry
>
