Re: Stored procedure to drop and create table for end user

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/10/04 

Date: Thu, 10 Jun 2004 18:24:37 +0200

I haven't looked at the other yet, but yes. Sp3 introduced the cross database ownership stuff. I recommend
that you read the readme file to sp3 and from there decide whether you want t enable this setting (at the
database or at the server level). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brian Henry" <brianiupmsdn@newsgroups.nospam> wrote in message
news:%23MHuRZwTEHA.3336@TK2MSFTNGP10.phx.gbl...
> here is another 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
>
>

Relevant Pages

  • Cross database stored procedure problems
    ... the following stored procedure does not work it ... SELECT permission denied on object 'UsersPermissions', database ... WHERE JETypeID IN ( ... from journalentrytypepermissions as JETP left outer join JournalEntryTypes ...
    (microsoft.public.sqlserver.programming)
  • Re: Cross database stored procedure problems
    ... >> now the database the stored proc is on is bene_development, ... >> select JETypeID from JournalEntryTypes where JournalEntryTypes.JETypeID ... as soon as i move to the or statement wher ethe select ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure to drop and create table for end user
    ... the following stored procedure does not work it ... SELECT permission denied on object 'UsersPermissions', database ... WHERE JETypeID IN ( ... from journalentrytypepermissions as JETP left outer join JournalEntryTypes ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure to drop and create table for end user
    ... Sp3 introduced the cross ... database ownership stuff. ... >> select JETypeID from JournalEntryTypes where JournalEntryTypes.JETypeID ... >> from journalentrytypepermissions as JETP left outer join ...
    (microsoft.public.sqlserver.programming)
  • Re: Cross database stored procedure problems
    ... > now the database the stored proc is on is bene_development, ... > select * from JournalEntrySubTypes ... > WHERE JETypeID IN ( ... as soon as i move to the or statement wher ethe select ...
    (microsoft.public.sqlserver.programming)