Cross database stored procedure problems
From: Brian Henry (brianiupmsdn_at_newsgroups.nospam)
Date: 06/10/04
- Next message: Scott Morris: "Re: "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.""
- Previous message: Brian Henry: "Re: Stored procedure to drop and create table for end user"
- Next in thread: Gregory A. Larsen: "Re: Cross database stored procedure problems"
- Reply: Gregory A. Larsen: "Re: Cross database stored procedure problems"
- Reply: Mingqing Cheng [MSFT]: "RE: Cross database stored procedure problems"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Jun 2004 12:17:20 -0400
here is an example.. the following stored procedure does not work it
returns
Server: Msg 229, Level 14, State 5, Procedure
BENESP_GetJournalEntrySubTypes, Line 10
SELECT permission denied on object 'UsersPermissions', database
'bene_users', owner 'dbo'.
now the database the stored proc is on is bene_development, and the users
database is universal the the company and is in bene_users (cross database
usage here)
/****** Object: Stored Procedure dbo.BENESP_GetJournalEntrySubTypes
Script Date: 4/23/2004 11:45:29 AM ******/
CREATE PROCEDURE [DBO].[BENESP_GetJournalEntrySubTypes]
@loginname varchar(50)
AS
select * from JournalEntrySubTypes
WHERE JETypeID IN (
select JETypeID from JournalEntryTypes where JournalEntryTypes.JETypeID in
(select jet.JETypeID
from journalentrytypepermissions as JETP left outer join JournalEntryTypes
AS JET
on JET.JETypeID = JETP.JETypeID
where permissionid in (select permissionid
from bene_users.dbo.UsersPermissions
where loginname = @loginname) or ((select count(*) from
bene_users.dbo.UsersPermissions where loginname = @loginname and
permissionid = 'canDoBAJournal') = 1)
OR ((select count(*) from bene_users.dbo.UsersPermissions where
loginname = @loginname and permissionid = 'canDoBillAssocJournal') = 1)
OR ((select count(*) from bene_users.dbo.UsersPermissions where
loginname = @loginname and permissionid = 'canDoCSAJournal') = 1)
OR ((select count(*) from bene_users.dbo.UsersPermissions where
loginname = @loginname and permissionid = 'canDoDirectorJournal') = 1)
OR ((select count(*) from bene_users.dbo.UsersPermissions where
loginname = @loginname and permissionid = 'canDoProducerJournal') = 1))
)
now if i run it just like this
/****** Object: Stored Procedure dbo.BENESP_GetJournalEntrySubTypes
Script Date: 4/23/2004 11:45:29 AM ******/
CREATE PROCEDURE [DBO].[BENESP_GetJournalEntrySubTypes]
@loginname varchar(50)
AS
select * from JournalEntrySubTypes
WHERE JETypeID IN (
select JETypeID from JournalEntryTypes where JournalEntryTypes.JETypeID in
(select jet.JETypeID
from journalentrytypepermissions as JETP left outer join JournalEntryTypes
AS JET
on JET.JETypeID = JETP.JETypeID ))
it works fine... as soon as i move to the or statement wher ethe select
count(*) from bene_users.dbo statement is the problem happens... and i get
Server: Msg 229, Level 14, State 5, Procedure
BENESP_GetJournalEntrySubTypes, Line 10
SELECT permission denied on object 'UsersPermissions', database
'bene_users', owner 'dbo'.
how would you correct this!? it seems to be a problem with cross database
usage
- Next message: Scott Morris: "Re: "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.""
- Previous message: Brian Henry: "Re: Stored procedure to drop and create table for end user"
- Next in thread: Gregory A. Larsen: "Re: Cross database stored procedure problems"
- Reply: Gregory A. Larsen: "Re: Cross database stored procedure problems"
- Reply: Mingqing Cheng [MSFT]: "RE: Cross database stored procedure problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
